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
RECORD COUNT FOR TABLE
![image image](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaFZd_ES8uVN8T4CTUXrb4yV3l_mYUKsTOkECBLe7UP7OAuwuGpX3DhBodP0ym0SiX8Z7n41yZuXDAeFKKVh2sInjDwOl9TpRMCe2kcxEUakhyphenhyphenRuArO_4YorET_3alDeOX7KhoFgbRLX0/?imgmax=800)
FOREIGN KEY CONSTRAINTS (Relationships)
![image image](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizuH9kyWMLInMMuSsY2wP27-QAAwhQMJfNhlXIIgdRqaLagNxoYqPTYqNisO9olML8iZJo5oO81T3LsRWRAgVUJNFiC32WrVv377pu0PzmiNVZtqlAge8Uj2rXfyDhcS_pvbooZIsbVN0/?imgmax=800)
NUMBER OF COLUMNS PER TABLE
SELECT TABLE_NAME, COUNT(*) AS COLUMN_COUNT
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
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
FOREIGN KEY CONSTRAINTS (Relationships)
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
ORDER BY TABLE_NAME
Comments
Post a Comment