1

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.

1
  • 1
    sysobjects and syscolumns are your friends.
    – Philᵀᴹ
    Nov 18, 2013 at 19:04

2 Answers 2

2

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.

2
  • Excellent! These are the best forum answers I've ever gotten! :) Keep up the great work guys! Nov 21, 2013 at 18:33
  • 1
    @PawełCzopowik Glad that it helped. Note that this is not a forum .. its a Q&A site and people here are just like you and me .. keep learning and eager to help. Welcome to the community :-)
    – Kin Shah
    Nov 22, 2013 at 19:54
1

Objects like sys.objects, sys.tables or sys.columns contain information about database objects, tables and columns. But as you noted, that do not contain information about system objects.

Most of these system views however can be prefixed with system_. So

SELECT * FROM sys.system_columns AS SC;

returns all the columns that are part of system tables in the current database.

There is also the all_ form that contains all objects, system and user:

SELECT * FROM sys.all_columns AS AC;

That query returns all columns in the current database, columns of system and non-system objects.

The following query gives you the complete list of these "system"/"all" objects:

SELECT  *
FROM    sys.system_objects
WHERE   schema_id = SCHEMA_ID('sys')
        AND (
        name LIKE 'system[_]%'
        OR name LIKE 'all[_]%'
        );
1
  • Great info! Thank you, i'll have to dig around and get familiar with this. Thanks for the great edit too. Nov 21, 2013 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.