Finding and listing all columns in a SQL Server database with default values

By: Tim Ford | Updated: 2008-06-04 | Comments (7) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Scripts

Problem
I was troubleshooting an issue last week on a vendor-developed database when they stated we needed to look at each one of the 50 tables in their database to make sure that all fields expecting default values, had default values assigned. Well, you can imaging how daunting a task that would be and I immediately raised the question: "Don't you have a better way to do this other than to open each table in SQL Server Management Studio to review the schema?" Their answer was a polite, but firm "No". At that point I decided to change their mind...

Solution
By querying three system tables in any database you can get the default value for each column of every table. Below is the core query. It returns the default value assigned to every user table in the current database. This query is compatible with both SQL 2000 and SQL 2005.

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" 
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  
WHERE SO.xtype = 'U'  
ORDER BY SO.[name], SC.colid

The sysobjects table provides us with the table metadata. In this case we're only interested in the table name. The syscolumns table stores metadata related to the individual columns for each table. In this example, we only require the column name. Finally, the default value metadata is provided courtesy of the syscomments table.

Running this query against the Northwind database yields the following results. (Some rows omitted for brevity.) Notice that it will return NULL default values due to the LEFT JOIN on the syscomments table.

Now my thoughts turned to what options existed for a tweaked version of the base query...

Option 1: Search for Specific Default Values

By editing the WHERE clause slightly, we can look for specific default values across all tables.

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" 
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  
WHERE SO.xtype = 'U' AND SM.TEXT = '(0)' 
ORDER BY SO.[name], SC.colid

Option 2: Return Information on Only Columns with Default Values

Modifying the core query's WHERE clause to omit NULL values in the syscomments.text table would do the trick here:

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" 
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  
WHERE SO.xtype = 'U' AND SM.TEXT IS NOT NULL 
ORDER BY SO.[name], SC.colid

however, better optimization occurs why altering the JOIN in the FROM clause from a LEFT JOIN to an INNER JOIN:

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" 
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
INNER JOIN dbo.syscomments SM ON SC.cdefault = SM.id  
WHERE SO.xtype = 'U' 
ORDER BY SO.[name], SC.colid

There is yet one other option entirely, taking advantage of the system catalog views in SQL 2005. While the previous queries provided me with the information I needed at the time, and worked in both SQL 2000 and SQL 2005, there is additional metadata relating to the default value (in actuality, a default constraint) to be mined out of a SQL 2000 instance. By basing the query exclusively on system catalog views we can get additional information not displayed in the previous queries.

SELECT ST.[name] AS "Table Name", SC.[name] AS "Column Name", SD.definition AS "Default Value", SD.[name] AS "Constraint Name"
FROM sys.tables ST INNER JOIN sys.syscolumns SC ON ST.[object_id] = SC.[id] 
INNER JOIN sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id 
ORDER BY ST.[name], SC.colid

So remember, just because you're told there is no better way, rely on your instincts as a DBA and dig in. You never know what you may come up with.

Next Steps

Review other tips on system catalog views.
Look at the other catalog views for your database objects.
Review the other columns available in sys.default_constraints

Last Updated: 2008-06-04

About the author

Tim Ford is a Senior Database Administrator with MindBody.

View all my tips

Related Resources

Become a paid author

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name

*Email

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

Monday, May 20, 2019 - 1:08:37 PM - sqlmonger

/*
Here is a version that works with the "modern" system views. It is a hack of code from the system procedure sp_help and other examples from this post.
Replace the IIF() statements with CASE statements if you are using a version prior to SQL 2012.
*/
SELECT OBJECT_NAME([sc].Object_id) AS [Table Name]
    , [sc].[column_id] as [Seq]
    , [sc].[name] AS [Column Name]
    , TYPE_NAME([sc].[user_type_id]) AS [User Type Name]
    , TYPE_NAME([sc].[system_type_id]) AS [Type Name]
    , [Length]
    , _.[Prec]
    , _.[Scale]
    , TYPE_NAME([sc].[system_type_id]) 
    + IIF(_.[format_len] = 0, ''
            , '(' + IIF( _.[Prec] = '     '
                    , cast([Length] as varchar)
                    , rtrim(_.[Prec]) + ', ' + rtrim(_.[Scale]))
                + ')' )
    as [Data Type]
    , IIF([sc].[is_nullable] = 1, 'Yes', 'No') AS [Nullable]
    , ISNULL(OBJECT_NAME([sc].[default_object_id]), '') AS [Default Name]
    , LOWER(ISNULL(
    (
        SELECT TOP 1 SUBSTRING(text, 2, LEN(text) - 2)
        FROM syscomments (NOLOCK)
        WHERE [id] = [sc].[default_object_id]
    ), '')) AS [DefaultValue]
FROM sys.all_columns (NOLOCK) AS [SC]
outer apply (
     select CONVERT(INT, [sc].[max_length]) AS [Length]
     , CASE
           WHEN CHARINDEX(TYPE_NAME([system_type_id]) + ',', N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,') > 0
           THEN CONVERT(CHAR(5), COLUMNPROPERTY(object_id, [name], 'precision'))
           ELSE '     '
       END AS [Prec]
     , CASE
           WHEN CHARINDEX(TYPE_NAME([system_type_id]) + ',', N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,') > 0
           THEN CONVERT(CHAR(5), OdbcScale( [system_type_id], [scale] ))
           ELSE '     '
       END AS [Scale]
, IIF(Charindex(TYPE_NAME([system_type_id]) ,N'decimal,real,float,numeric,datetimeoffset,char,varchar,nchar,nvarchar') > 0
,1,0) as [format_len]
) as _

Wednesday, October 17, 2012 - 8:35:20 AM - JT02451	Back To Top
Any idea how to do a column_name,count(*) on ALL columns on ALL tables within a database schema? Must it be done cursively with a script or TransactSQL, or can it be done in a single query? I'd be interested in writing the results to a table. Thanks

Thursday, August 30, 2012 - 12:01:45 AM - jadav suresh

how to find spacific column in alll database.

plz solve my problem.

Friday, July 25, 2008 - 12:14:07 AM - sean.x.li	Back To Top
I think this is also workable in SQL 2K

Sunday, June 15, 2008 - 11:15:34 AM - 24Seven

There is a far simpler technique in SQL 2005+

Select *
From INFORMATION_SCHEMA.COLUMNS
Where COLUMN_DEFAULT Is Not Null

Wednesday, June 11, 2008 - 12:39:14 AM - impala

how about this one?

SELECT t.name AS TableName,

c.name AS ColumnName,

ty.name AS ColumnType,

c.length AS Length,

c.isnullable AS AllowNulls,

CASE WHEN EXISTS (SELECT TOP 1 indid FROM sysindexkeys WHERE id = t.id AND indid = colid AND colid = c.colid AND keyno=c.colid)

THEN 1 ELSE 0 END AS IsPrimaryKey,

lower(isnull((select top 1 substring(text, 2, len(text) -2) from syscomments (NOLOCK) where id = c.cdefault), '')) AS DefaultValue

FROM sysobjects t (NOLOCK),

syscolumns c (NOLOCK),

systypes ty (NOLOCK)

WHERE c.id = t.id

AND c.xtype = ty.xusertype

and t.xtype='U'

and t.name <> 'dtproperties'

ORDER BY t.name, c.colorder

Thursday, June 05, 2008 - 12:15:20 AM - Ignacio de Tomas	Back To Top
Really very useful