Sql-server – How to get columns from system tables or views like sys.database_files (SQL Server 2012)

sql-server-2012system-tables

How can I write a query to return all columns of, for example, the sys.database_files table? I see many suggestions for using information_schema.columns for getting table names for user databases/tables, but i can't figure out how to do this for system tables.

I see that the sys.database_files is a view under the master table. I'm using SQL Server 2012.

Best Answer

Since you are on SQL Server 2012, best is to use the new DMF sys.dm_exec_describe_first_result_set. This dmf will take a transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.

so for example, you want to find out the columns names along with the datatypes and precison for sys.database_files, simply use 

SELECT 
    name, 
    system_type_name, 
    is_nullable
 -- add/remove columns as per your need ....
FROM 
    sys.dm_exec_describe_first_result_set
    (
        N'SELECT * FROM sys.database_files;', NULL, 0
    ) AS f
ORDER BY
    column_ordinal;

Below are the results :

enter image description here

For versions below sql 2012:

SELECT object_NAME(c.object_id) AS OBJECT_NAME
    ,c.NAME AS COLUMN_NAME
    ,t.NAME AS DATA_TYPE
    ,c.max_length AS MAX_LENGTH
FROM sys.all_columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE object_name(c.object_id) = 'database_files'

Excellent post is written by Aaron Bertrand -- SQL Server v.Next (Denali) :Metadata enhancements which explains this in much details. Also, refer to the CAVEATS in the section down the article.

I see many suggestions for using information_schema.columns for getting table names for user databases/tables.

Avoid using INFORMATION_SCHEMA views. Worth reading Aaron's post on The case against INFORMATION_SCHEMA views.

Related Question