SQL Server: need a list of tables having DEFAULT constraint.

Hi, I need a list of tables that have DEFAULT constraint on at least one of the column. Would be nice to be able to filter by column datatype and/or name.
Thanks.
quasar_eeAsked:
Who is Participating?
 
mikeyd234Connect With a Mentor Commented:
Try this,  will output all default_constraints columns with their data types and the parent table name for each.

SELECT sys.default_constraints.name, 
       sys.all_columns.name AS column_name, 
       ty.name              AS column_type, 
       sys.tables.name      AS table_name 
FROM   sys.all_columns 
       INNER JOIN sys.tables 
               ON sys.all_columns.object_id = sys.tables.object_id 
       INNER JOIN sys.schemas 
               ON sys.tables.schema_id = sys.schemas.schema_id 
       INNER JOIN sys.default_constraints 
               ON sys.all_columns.default_object_id = 
                  sys.default_constraints.object_id 
       LEFT OUTER JOIN sys.types AS ty 
                    ON sys.all_columns.system_type_id = ty.system_type_id 
WHERE  ( sys.schemas.name = 'dbo' )
-- AND all_columns.name = 'columnname'  -- filter by column name
-- AND ty.name  = 'nvarchar'                   -- filter by data type
 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Copy-paste the below T-SQL into your SSMS, execute it to verify it works, then delete the WHERE clause
CREATE TABLE boo (foo varchar(100) DEFAULT 'banana')

SELECT t.name, c.name, dc.definition
FROM sys.tables t
	JOIN sys.columns c ON t.object_id = c.object_id
	JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
WHERE c.name = 'foo'

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.