Get Column Attributes for all SQL Server Tables that Match Criteria

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:

Results of join between sys.columns and sys.types

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: <div class="codediv">
<pre>
DECLARE @procedure nvarchar(512) = N'sys.sp_who2'; EXECUTE @procedure;
</pre>
</div> 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: <div class="codediv">
<pre>
DECLARE @dbname nvarchar(128) = N'tempdb'; EXECUTE @dbname.sys.sp_who2;
</pre>
</div> But we can say: <div class="codediv">
<pre>
DECLARE @dbname nvarchar(128) = N'tempdb'; DECLARE @procedure nvarchar(768) = @dbname + N'.sys.sp_who2'; EXECUTE @procedure; – effectively @dbname.sys.sp_who2
</pre>
</div> 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 <a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql" target="_blank" rel="noopener"> sys.sp_executesql</a> 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. <div class="codediv">
<pre>
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'';';
</pre>
</div> 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 <code>@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 sysnameINSERT @dbs(db) VALUES(N'msdb'),(N'tempdb'),(DB_NAME());
DECLARE @dbname nvarchar(128WHILE 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(128DECLARE 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:

Resources

Community

Subscribe