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 list of databases from Sql Server – Stack Overflowhttp://stackoverflow.com/questions/147659/get-list-of-databases-from-sql-serverHow can I get the list of available databases on a SQL server instance? I’m planning to make a list of them in a combo box in vb.net.

Get a list of all database on a SQL Server:
SELECT name FROM master..sysdatabases

List All Tables in a Database – sqlserverplanet.comhttp://sqlserverplanet.com/dba/list-all-tables-in-a-databaseThere are a few methods for listing all the tables in a database. Some methods will be able to show more specifics than others due to the capabilities inherent in the tables being joined. I’ll…

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! 🙂

Leave a Reply

Your email address will not be published.