By: Aaron Bertrand | Updated: 2019-10-02 | Comments (2) | Related: More > Database Administration
Problem
When documenting a system, it is sometimes necessary to gather things like the column names, data types, and other attributes across a large number of tables. While you could expand the Columns node in Object Explorer in SSMS one table at a time, this is a tedious and error-prone approach. Object Explorer gets all of its information from the metadata already in the system, so is there an easier way to query for this data?
Solution
Again, this information is all available in catalog views, the trick is just querying it the right way. Let's say we are interested in each column of each table: the ordinal position, the name, the data type, and whether it is nullable. In all currently supported versions of SQL Server (2012 and up), there is a handy function that allows you to get all the columns for a T-SQL query, sys.dm_exec_describe_first_result_set. This function is superior to querying the information from sys.columns, mainly because you don't have to join to sys.types, or embed complicated logic to deal with translation; for example, CASE expressions that change -1 to max, eliminating types that have synonyms, or cutting nvarchar lengths in half – but only if they aren't max. (You can see the type of query you end up in an earlier tip, "SQL Server Data Type Consistency.")
As a quick example, let's create this table:
SELECT a = CONVERT(tinyint,1), b = 2, c = N'foo', d = CONVERT(nvarchar(max),1)
INTO dbo.example;
If we look at sys.columns and sys.types, we can run the following query:
SELECT c.column_id, c.name, system_type_name = t.name, c.max_length, c.is_nullable
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
WHERE c.[object_id] = OBJECT_ID(N'dbo.example')
ORDER BY c.column_id;
But this gets us a very messy result set, because it includes relations that are not relevant (sysname is a synonym for nvarchar), represents max_length as the number of bytes rather than characters, and indicates max with -1:
I won't go into how to improve the above query because, by contrast, the newer function is simpler, and produces a much more usable result:
SELECT column_ordinal, name, system_type_name, user_type_name, is_nullable
FROM sys.dm_exec_describe_first_result_set
(
N'SELECT * FROM dbo.example;', N'', 0
) ORDER BY column_ordinal;
Results:
Right off the bat, this is more useful (I included the user_type_name column to show that sysname doesn't become unnecessarily complicated with this query).
Next, you need to make the script dynamic, so that you don't have to hard-code each table name into the query. We can do this using CROSS APPLY:
SELECT
[Number] = f.column_ordinal,
[Name] = f.name,
[Type] = f.system_type_name,
[Nullable] = f.is_nullable,
SourceTable = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
CROSS APPLY sys.dm_exec_describe_first_result_set
(
N'SELECT * FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name), N'', 0
)AS f
ORDER BY SourceTable, [Number];
Narrowing it down, we can pass in a pattern, so that we only bother with the
columns for tables that match a pattern name we're interested in. In this
case, we'll pass in a pattern that will bring back our dbo.example table from
above (and any other names that start with example):
DECLARE @pattern nvarchar(255) = N'example%'; SELECT
[Number] = f.column_ordinal,
[Name] = f.name,
[Type] = f.system_type_name,
[Nullable] = f.is_nullable,
SourceTable = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
CROSS APPLY sys.dm_exec_describe_first_result_set
(
N'SELECT * FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name), N'', 0
) AS f
WHERE t.name LIKE @pattern
ORDER BY SourceTable, [Number];
The results should look familiar (except I added a column to indicate which table it came from):
So now, you could get the columns from a specific table (by setting
@pattern = 'that table name'),
all tables that match a pattern, or all tables in the entire database by setting
@pattern = '%'). But what if
you needed columns for any of those groups of tables, but across multiple databases?
I've got a shortcut for that too, and it has to do with functionality that's
hard to document… and even harder to discover on your own.
EXECUTE has the ability to execute a stored procedure directly, but it's
not as well known that it can execute a string. I'm not talking about the
pattern where you pass a valid query in and use parentheses (EXECUTE('some
query');), I'm talking about a different
pattern, where you pass the name of a stored procedure as a string. For example:
DECLARE @procedure nvarchar(512) = N'sys.sp_who2';
EXECUTE @procedure;
We can use this to our advantage, so that we can pass database name as a parameter, so to speak. We can't do this:
DECLARE @dbname nvarchar(128) = N'tempdb';
EXECUTE @dbname.sys.sp_who2;
But we can say:
DECLARE @dbname nvarchar(128) = N'tempdb';
DECLARE @procedure nvarchar(768) = @dbname + N'.sys.sp_who2';
EXECUTE @procedure; -- effectively @dbname.sys.sp_who2
If you're not seeing how this can help us yet, stay with me; we're going to switch from using sp_who2 directly, to passing it into sys.sp_executesql in the specified database. This allows us both to run the query fully in the context of that database, and allows us to pass parameters to the eventual query string.
DECLARE @dbname nvarchar(128) = N'tempdb';
DECLARE @procedure nvarchar(512) = N'sys.sp_who2';
DECLARE @exec nvarchar(768) = @dbname + N'.sys.sp_executesql';
EXECUTE @exec @procedure, N'@loginame sysname', N'active';
-- effectively @dbname.sys.sp_executesql N'EXEC sys.sp_who2 @loginame = N''active'';';
So now, instead of hard-coding the database, we can use a loop or a cursor to
populate each database name we care about. And we can change
@procedure to be the query that pulls back the metadata
information, and pass @pattern in as an parameter.
Let's do this for the current database first:
DECLARE @pattern nvarchar(255) = N'example%'; DECLARE @query nvarchar(max) = N'
SELECT
[Number] = f.column_ordinal,
[Name] = f.name,
[Type] = f.system_type_name,
[Nullable] = f.is_nullable,
SourceTable = QUOTENAME(s.name) + N''.'' + QUOTENAME(t.name)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
CROSS APPLY sys.dm_exec_describe_first_result_set
(
N''SELECT * FROM '' + DB_NAME() + N''.'' + QUOTENAME(s.name) + N''.'' + QUOTENAME(t.name),
N'''', 0
) AS f
WHERE t.name LIKE @pattern
ORDER BY SourceTable, [Number];'; DECLARE @dbname nvarchar(128) = DB_NAME(); -- where you created dbo.example
DECLARE @exec nvarchar(768) = @dbname + N'.sys.sp_executesql';
EXECUTE @exec @query, N'@pattern nvarchar(255)', @pattern;
You should see the exact same result as above, except the database name will be prefixed to the SourceTable column.
Now, as I suggested, we can easily extend this by declaring a set of databases
to loop through, or pulling directly from sys.databases. For brevity, I'm
going to leave out the bulk of the @query string in
these examples, but they stay the same as the above.
Here is a loop:
DECLARE @pattern nvarchar(255) = N'example%'; DECLARE @query nvarchar(max) = N'
SELECT
...
ORDER BY SourceTable, [Number];'; DECLARE @dbs table(db sysname); INSERT @dbs(db) VALUES(N'msdb'),(N'tempdb'),(DB_NAME()); DECLARE @dbname nvarchar(128); WHILE EXISTS (SELECT 1 FROM @dbs)
BEGIN
SELECT TOP (1) @dbname = db FROM @dbs;
DECLARE @exec nvarchar(768) = @dbname + N'.sys.sp_executesql';
EXECUTE @exec @query, N'@pattern nvarchar(255)', @pattern;
DELETE @dbs WHERE db = @dbname;
END
And here is a cursor:
DECLARE @pattern nvarchar(255) = N'example%'; DECLARE @query nvarchar(max) = N'
SELECT
...
ORDER BY SourceTable, [Number];'; DECLARE @dbname nvarchar(128); DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases -- WHERE status = 0, database_id > 4, etc...; OPEN dbs; FETCH NEXT FROM dbs INTO @dbname; WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @exec nvarchar(768) = @dbname + N'.sys.sp_executesql';
EXECUTE @exec @query, N'@pattern nvarchar(255)', @pattern;
FETCH NEXT FROM dbs INTO @dbname;
END CLOSE dbs; DEALLOCATE dbs;
These examples would return a resultset per database, but you could easily change them to insert the output of EXECUTE into a #temp table, and then query that #temp table however you need to. You could also use this same technique to query metadata across linked servers, but I'll leave both of these as exercises for future articles.
Next Steps
Read on for related tips and other resources:
- Execute Dynamic SQL commands in SQL Server
- Dynamic SQL execution on remote SQL Server using EXEC AT
- Run a Dynamic Query against SQL Server without Dynamic SQL
- Making SQL Server metadata queries easier with these new Views
- Find all SQL Server columns of a specific data type using Policy Based Management
- Search all string columns in all SQL Server databases
- Change All Computed Columns to Persisted in SQL Server
- SQL Server Dynamic SQL Tips
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-10-02
