info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools Services
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Get Table Information
by BF (Principal Consultant; Architecture; Engineering)
2022-05-10








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