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