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.
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.
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
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 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')
Table information
This query will show you the tables in your database, along with disk space information about each of the tables.
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
Here is a simpler query to show the tables and the sizes in megabytes, with the largest tables at the top.
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
Database information
This query will show you the databases that SQL Server knows about, along with their sizes.
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;
Monitoring connections
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 queries
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 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