--Get Tables & Columns for a specific database
select schema_name(tab.schema_id) as schema_name, tab.name as table_name, col.column_id, col.name as column_name, t.name as data_type, col.max_length, col.precision from sys.tables as tab inner join sys.columns as col on tab.object_id = col.object_id left join sys.types as t on col.user_type_id = t.user_type_id order by schema_name asc, table_name asc, column_id;
--Get Tables, Row Counts & Space for a specific database
SELECT @@servername as servername, db_name() as databasename, s.name AS schemaname, t.name AS tablename, p.rows AS rowcounts, SUM(a.total_pages) * 8 AS totalspaceKB, SUM(a.used_pages) * 8 AS usedspaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS unusedspaceKB, getdate() as collectiondate 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 --and t.name = '' GROUP BY t.name, s.name, p.Rows
--Get Tables, Row Counts & Space for all user databases
SELECT name as databasename INTO #userdatabases FROM sys.databases WHERE database_id > 4
CREATE TABLE #tablesizes( servername sysname, databasename sysname, schemaName sysname, tablename sysname, rowcounts INT, totalspaceKB DECIMAL(18,2), usedspaceKB DECIMAL(18,2), unusedspaceKB DECIMAL(18,2), collectiondate datetime );
DECLARE @command VARCHAR(MAX);
SET @command = ' USE [?]
IF DB_NAME() IN (SELECT databasename FROM #userdatabases) BEGIN INSERT #tablesizes SELECT @@servername as servername, db_name() as databasename, s.name AS schemaname, t.name AS tablename, p.rows AS rowcounts, SUM(a.total_pages) * 8 AS totalspaceKB, SUM(a.used_pages) * 8 AS usedspaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS unusedspaceKB, getdate() as collectiondate 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 --and t.name ='' GROUP BY t.name, s.name, p.Rows END'; EXEC sp_MSforeachdb @command
select * from #tablesizes order by 2 asc, 3 asc, 4 asc drop table #tablesizes drop table #userdatabases
|
|
|
|
|