SQL Server database and table listing
I generally work with MySQL, not MS SQL Server. So when I needed some basic automated info about a SQL Server’s database/table layout I was thankful for the following two pages:
Get a list of all database on a SQL Server:
SELECT name FROM master..sysdatabases
Get a list of all tables in a database, including rowcount and space used:
SELECT *
FROM (
SELECT
TableName = t.TABLE_SCHEMA + '.' + t.TABLE_NAME
,[RowCount] = SUM(sp.[ROWS])
,Megabytes = (8 * SUM(CASE WHEN sau.TYPE != 1 THEN sau.used_pages WHEN sp.index_id < 2 THEN sau.data_pages ELSE 0 END)) / 1024
FROM INFORMATION_SCHEMA.TABLES t
JOIN sys.partitions sp
ON sp.object_id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME)
JOIN sys.allocation_units sau
ON sau.container_id = sp.partition_id
WHERE TABLE_TYPE = 'BASE TABLE'
GROUP BY
t.TABLE_SCHEMA + '.' + t.TABLE_NAME
) A
ORDER BY TableName
There is a lot more great SQL Server info and queries on those two pages so I highly recommend checking out the fulltext original articles! 🙂