--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
|
|
|
|
|
|