Tuesday, May 21, 2013

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

No comments: