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