When you have the needs to figure out a MS SQL database’s structure, here are 3 SQL statements that would give you a quick start. While they won’t give you a full picture of the whole database they are definitely going to lead you in a right direction with a quick view of what’s in there right now.
List the name of all the tables in a database
USE databasename SELECT * FROM information_schema.tables ORDER BY TABLE_NAME
Replace the real database name in, you are off to go to get a list of tables in that database in the order of the table name.
List the column names of all the tables in a database
Now since we know how many tables in a database, let’s find out what the columns are in each table.
USE databasename SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID ORDER BY schema_name, table_name;
If you need to know the column details in each table, do the following to each table:
exec sp_columns 'table1' exec sp_columns 'table2' ... ...
List the tables with row counts and space consumption
If you are investigating a fairly large database, it would be helpful knowing what tables to look first. Execute the following statements and you will get a more clear picture of what you should look into first.
Use databasename SELECT t.NAME AS billings, s.Name AS SchemaName, 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 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 GROUP BY t.Name, s.Name, p.Rows ORDER BY t.Name
That’s about it. These statements have helped me a lot in number of occasions where I need to figure out a new database in order to do some customization. I hope they will help you too down the road.