Little trick to query a SQL Server database’s schema
This is the easiest way to query the schema of a database I know of:
- First go to SQL Server Management Studio, expand the databases node and go to the list of tables. In this example I’m using the AdventureWorks database:
- Launch the SQL Profiler and start a new trace.
- Right-click the tables node and select “Refresh”:
- Now you can see the the query that the SSMS used in the SQL Profiler:
And that’s it, you just have to customize it to fit your needs. You can apply this to query other type of objects of course, like indexes, keys and so on (basically anything you see in SSMS). For example, the following query will list all the columns and the table they belong to:
SELECT
tbl.name as TableName,
clmns .name AS [Name],
CAST (ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],
CAST (ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
usrt.name AS [DataType],
CAST (clmns.precision AS int) AS [NumericPrecision],
CAST (clmns.scale AS int) AS [NumericScale],
clmns.is_nullable AS [Nullable],
clmns.is_computed AS [Computed]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
ORDER BY tbl.name, clmns.name
To obtain it I refreshed the list of columns in a table, and from the resulting query I removed a couple of joins and the where clause, and added a different ORDER BY clause.
An extra bonus of this trick is that by examining the queries SSMS uses you’ll learn more about SQL Server itself.
Hope it helps :)
