Last active
November 24, 2021 19:31
-
-
Save lockworld/06b550b5ce311d629443852f3827cfc3 to your computer and use it in GitHub Desktop.
SQL Server Database and Server commands (These are for when you need to go beyond just table-level access).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
t.NAME AS TableName, | |
p.rows AS RowCounts, | |
CONVERT(DECIMAL,SUM(a.total_pages)) * 8 / 1024 / 1024 AS TotalSpaceGB, | |
SUM(a.used_pages) * 8 / 1024 / 1024 AS UsedSpaceGB , | |
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 / 1024 AS UnusedSpaceGB | |
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 IN ('TABLE01','TABLE02') | |
AND t.is_ms_shipped = 0 | |
AND i.OBJECT_ID > 255 | |
GROUP BY | |
t.Name, s.Name, p.Rows | |
ORDER BY | |
UsedSpaceGB DESC, t.Name | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Use this query to find the start time, percent complete, and estimated completion time for a running backup or restore process | |
*/ | |
SELECT session_id AS SPID | |
,command | |
,percent_complete | |
,start_time | |
,dateadd(second, estimated_completion_time / 1000, getdate()) AS estimated_completion_time | |
,a.TEXT AS Query | |
FROM sys.dm_exec_requests r | |
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a | |
WHERE r.command IN ( | |
'BACKUP DATABASE' | |
,'RESTORE DATABASE' | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select * from INFORMATION_SCHEMA.COLUMNS | |
order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--https://stackoverflow.com/a/1171031 | |
SELECT | |
[name] | |
,create_date | |
,modify_date | |
FROM | |
sys.tables | |
ORDER BY create_date DESC, modify_date DESC |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' | |
ORDER BY TABLE_NAME |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This set of gists helps when working with tables in SQL Server |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment