SQL stuff

Here are SQL scripts you may find useful. These are all for SQL Server unless otherwise noted, and if a specific version of a database application is required, I will try to remember to call that out as well.

Search all triggers for specific text

This search will find specified text on triggers attached to any table, so it can be a real time saver when you are looking for that needle in your SQL Server haystack.

DECLARE @tableName NVARCHAR(255)
 
DECLARE db_cursor CURSOR FOR
SELECT
    t.NAME AS TableName
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
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
AND p.rows = 0
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TableName
 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @tableName  
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
    DBCC CHECKIDENT (@tableName, RESEED, 0)
    FETCH NEXT FROM db_cursor INTO @tableName
END
 
CLOSE db_cursor  
DEALLOCATE db_cursor

Reset identity for tables with zero records

Just on the off chance that you want to reset the identity back to 0 so that your new record start at 1 again, here is a query based off the Table information query below that will perform that task.

SELECT p.name AS ParameterName, t.name AS ParameterType, p.max_length AS ParameterLength
FROM sys.parameters AS p
JOIN sys.types AS t ON t.user_type_id = p.user_type_id
WHERE object_id = OBJECT_ID('YourProcedureName')

If you have any tables in your database with 0 records, and the table schema does not include a primary key field with identity, you will get an error in the console on the DBCC CHECKIDENT line, but the script will just continue forward.

Stored procedure parameters

This query will return the parameters for a given stored procedure, just in case you do not have sufficient permissions to look at and edit the stored procedure.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(SUM(a.total_pages) * 8 / 1024.0, 2) AS NUMERIC(36,2)) AS TotalSpaceMB, 
    CAST(ROUND(SUM(a.total_pages) * 8 / 1024.0 / 1024.0, 2) AS NUMERIC(36,2)) AS TotalSpaceGB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(SUM(a.used_pages) * 8 / 1024.0, 2) AS NUMERIC(36,2)) AS UsedSpaceMB, 
    CAST(ROUND(SUM(a.used_pages) * 8 / 1024.0 / 1024.0, 2) AS NUMERIC(36,2)) AS UsedSpaceGB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    UsedSpaceKB desc

Table information

This query will show you the tables in your database, along with disk space information about each of the tables.

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as MB
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY MB DESC

Here is a simpler query to show the tables and the sizes in megabytes, with the largest tables at the top.

SELECT 
    [TYPE] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' 
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END 
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
order by A.TYPE desc, A.NAME;

Database information

This query will show you the databases that SQL Server knows about, along with their sizes.

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID

Monitoring connections

SELECT TOP 5 query_stats.query_hash AS "Query Hash",
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC

Monitoring queries

SELECT
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
FROM
    (SELECT TOP 50
        qs.plan_handle,
        qs.total_worker_time
    FROM
        sys.dm_exec_query_stats qs
    ORDER BY qs.total_worker_time desc) AS highest_cpu_queries
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC

Monitoring query plans

SELECT
    highest_cpu_queries.plan_handle,
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
FROM
    (SELECT TOP 50
        qs.plan_handle,
        qs.total_worker_time
    FROM
        sys.dm_exec_query_stats qs
    ORDER BY qs.total_worker_time desc) AS highest_cpu_queries
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC

References

Monitoring Azure SQL Database using dynamic management views

Comments are closed.