billkiddo

CREATE LOGIN [uefa_play] WITH PASSWORD=N'$AAAAA'

CREATE USER uefa_play FROM LOGIN uefa_play; 

ALTER ROLE dbmanager ADD MEMBER uefa_play; 

GRANT ALL TO uefa_play;  

EXEC sp_addrolemember N'db_owner', N'uefa_play'

billkiddo

SELECT
s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

billkiddo

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
    , [sJOB].[description] AS [JobDescription]
    , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]

billkiddo

SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
mf.physical_name PhysicalFileLocation,
dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY FreeSpaceInMB ASC
GO

billkiddo

// based on https://msdn.microsoft.com/en-us/library/hh925568%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396
using System;
using Microsoft.Win32;

namespace NETFWK
{
    class Program
    {
        static void Main(string[] args)
        {

billkiddo

USE X;
GO

ALTER DATABASE X
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (X_log, 1);
GO

billkiddo

SELECT so.name as TableName, MAX(si.rows) as [RowCount] 
FROM sysobjects so
JOIN sysindexes si ON si.id = OBJECT_ID(so.name)
WHERE so.xtype = 'U'
GROUP BY so.name 
ORDER BY [RowCount] DESC

billkiddo

CREATE PROCEDURE sp_who3
(
    @SessionID int = NULL
)
AS
BEGIN
SELECT
    SPID                = er.session_id
    ,Status             = ses.status
    ,[Login]            = ses.login_name

billkiddo

/*
https://support.microsoft.com/kb/255596?wa=wsignin1.0
*/
USE master
GO
create procedure sp_lock2 
@spid1 int = NULL,      /* server process id to check for locks */ 
@spid2 int = NULL       /* other process id to check for locks */ 
as

billkiddo

CREATE PROCEDURE [dbo].[usp_test]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @trancount int;
    SET @trancount = @@trancount;
    BEGIN TRY
	   IF @trancount = 0
		  BEGIN TRANSACTION;
	   ELSE