Copy and Paste

Quick page of some common queries using object and data management views.

Use the copy command in the upper right of each code block.


Missing Indexes

--sys.dm_db_missing_index_groups
--sys.dm_db_missing_index_group_stats
--sys.dm_db_missing_index_details
SELECT
        GETDATE() AS runtime
        ,@@Servername
        ,REPLACE(REPLACE(mid.statement,']',''),'[','') AS tablename
        ,migs.avg_total_user_cost
        ,migs.avg_user_impact
        ,migs.user_seeks
        ,migs.user_scans
        
        --Pulled from the internet.
        ,CONVERT (DECIMAL (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure
        ----------------------------
        --Index creation syntax
        ,'CREATE INDEX missing_index_' + 'INDEX_NAME ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + 
                    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL
                         THEN ','
                         ELSE ''
                         END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '')
        AS create_index_statement
        ----------------------------
        ,mid.database_id
        ,mid.[object_id]
FROM    sys.dm_db_missing_index_groups AS mig INNER JOIN
        sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle INNER JOIN
        sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
WHERE    1=1
         AND mid.statement NOT LIKE '%msdb%'
ORDER BY
        user_seeks desc,
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

--EXEC sp_helptext 'schecma.name'

Index Statistics: Determine Fragmentation

--sys.dm_db_index_physical_stats
SELECT  @@servername AS ServerName,
        s.[name] AS 'Schema',
        t.[name] AS 'Table',
        i.[name] AS 'Index',
        idxstats.avg_fragmentation_in_percent,
        idxstats.page_count
FROM    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) idxstats INNER JOIN
        sys.tables t ON t.object_id = idxstats.object_id INNER JOIN
        sys.schemas s ON t.schema_id = s.schema_id INNER JOIN
        sys.indexes i ON i.object_id = idxstats.object_id AND idxstats.index_id = i.index_id
WHERE   1=1 
        AND idxstats.database_id = DB_ID()
        AND i.[name] IS NOT NULL
        AND idxstats.avg_fragmentation_in_percent > 0
ORDER BY
        idxstats.avg_fragmentation_in_percent DESC;

SQL Server Agent

--dbo.sysjobsstep - Contains the information for each step in a job to be executed by SQL Server Agent. This table is stored in the msdb database.
--dbo.sysjobs - Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database
SELECT  @@SERVERNAME AS ServerName,
        s.step_id AS 'StepID',
        j.[name] AS 'SQLAgentJobName',
        s.[database_name] AS 'DBName',
        s.command AS 'Command'
FROM    msdb.dbo.sysjobsteps s INNER JOIN 
        msdb.dbo.sysjobs AS j ON s.job_id = j.job_id
WHERE   1=1 
        AND s.command LIKE '%example_command%';

Column Names

--sys.schemas
--sys.tables
--sys.columns
--sys.types
SELECT  @@SERVERNAME as ServerName,
        s.name AS SchemaName, 
        t.name AS TableName, 
        c.name AS ColumnName, 
        ty.name as DataType,
        CONCAT('SELECT * FROM ',s.name,'.',t.name,';') as QuickQuery
from    sys.schemas s LEFT OUTER JOIN
        sys.tables t ON s.schema_id = t.schema_id INNER JOIN
        sys.columns c ON t.object_id = c.object_id INNER JOIN
        sys.types ty ON ty.user_type_id = c.user_type_id
WHERE   1=1
ORDER BY 1,2,3,4;

Cursor

DECLARE @vMyVariable VARCHAR(100);
DECLARE test_cursor CURSOR FOR (SELECT 'Hello World' UNION SELECT 'Goodbye World');

OPEN test_cursor;
FETCH NEXT FROM test_cursor INTO @vMyVariable;
	WHILE @@FETCH_STATUS = 0
		BEGIN
		PRINT(CONCAT('The value is ', @vMyVariable));
		FETCH NEXT FROM test_cursor INTO @vMyVariable;
		END
CLOSE test_cursor;
DEALLOCATE test_cursor;

sys.sql_modules: Returns the definition of a stored procedure. sp_helptext ‘MyStoredProcedure’ can also be used.

SELECT  o.name, o.type_desc, o.create_date, o.modify_date, m.*
FROM    sys.sql_modules m LEFT OUTER JOIN
        sys.all_objects o on m.object_id = o.object_id
WHERE   m.definition like  '%myText%';

Ascii Function: Returns the ascii values of a string.

DECLARE @vMyString VARCHAR(100) = 'Hello World';
DECLARE @vLength INTEGER = LEN(@vMyString);
DECLARE @vPosition INTEGER = 0;
DECLARE @vAscii VARCHAR(MAX) = '';

WHILE @vLength >= @vPosition
BEGIN
  SELECT @vAscii = @vAscii + CONCAT(ASCII(SUBSTRING(@vMyString,@vPosition,1)),',');
  SELECT @vPosition = @vPosition + 1;
END;

SET @vAscii = SUBSTRING(@vAscii,2,LEN(@vAscii)-2);--Removes beginning and ending commas
SELECT @vAscii AS AsciiCodes;

Random Numbers

Use the function ABS(CHECKSUM(NEWID()) % 10) to generate a random number between 1 and 10. The following loop generates 10,000 random numbers between 1 and 10 and then provides proof that each number has an equal chance of being selected.

DROP TABLE IF EXISTS #RandomNumbers;
GO
CREATE TABLE #RandomNumbers
(
MyRandomNumber INTEGER NOT NULL
);
GO

DECLARE @i INTEGER = 1;
DECLARE @loops INTEGER = 100000;
WHILE @i <= @loops
BEGIN
    INSERT INTO #RandomNumbers SELECT ABS(CHECKSUM(NEWID()) % 10) + 1;
    SET @i = @i + 1;
END
GO
----------------------------------------------------------------
----------------------------------------------------------------
WITH cte_Total AS
(
SELECT COUNT(MyRandomNumber) AS MyTotal FROM #RandomNumbers
),
cte_Count AS
(
SELECT  MyRandomNumber, COUNT(*) AS MyCount
FROM    #RandomNumbers a CROSS JOIN
        cte_Total b
GROUP BY MyRandomNumber
)
SELECT  MyRandomNumber, 
        MyCount, 
        MyCount / CAST(MyTotal AS NUMERIC(10,2))
FROM    cte_Count a CROSS JOIN
        cte_Total b;
GO