SQL Statements to Get Basic Info About Tables

Here are two scripts I found that will help you generate basic info about your database.  This data can be useful when beginning the level of effort for a data migration.
NUMBER OF COLUMNS PER TABLE
SELECT TABLE_NAME, COUNT(*) AS COLUMN_COUNT
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME

image


RECORD COUNT FOR TABLE
SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC



image

FOREIGN KEY CONSTRAINTS (Relationships)
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY TABLE_NAME

image

Comments

Popular posts from this blog

Add User As Local Administrator On Domain Controller

Calling Dataverse Web API in PowerShell using Client Credentials

Windows Server 2008R2 VMs Shut Down After 1 to 2 Hours