Finding Table Defaults, Primary and Unique Keys and Foreign Keys with sp_helpconstraint

A little while back I wrote a post going over sp_help in a beginning effort to find table dependency information.  https://www.sqlrx.com/using-sp_help-to-find-object-dependencies/.  That expanded to reviewing sys.dm_sql_referenced_entities & sys.dm_sql_referenced_entities  (https://www.sqlrx.com/finding_things_sql_that_reference_a_table/ & https://www.sqlrx.com/how-to-find-the-objects-and-columns-a-stored-procedure-or-view-uses/ ) and sys.sql_expression_dependencies (https://www.sqlrx.com/using-sys-sql_expression_dependencies-as-a-single-source-to-find-referenced-and-referencing-objects/  )

Now I want to loop back just a bit and expand on more information that is found in sp_help and alternative ways to find much the same information using queries.  My thought is that while the “help” stored procedures and other dmf’s and dmv’s are great, they often return multiple results sets and sometimes they are not exactly or maybe more than what you need so it is important to know that there are queries to get to the same info.  This will allow me to eventually get to the point where I can use queries to build some scripting statements.

Let’s take a fast look again at sp_help:

EXEC sp_help 'Sales.Orders'

As you can see multiple result sets are returned.  You can see the table columns, foreign keys, defaults and things that reference it.  This is great but since my ultimate goal is to be able to eventually script the things that are seen here, this will not work.  So let’s get some queries together that will individually provide much the same information.

I’ll start with column info for the table.  In my example, I have filtered for a single table but if you need to get info for all tables in the database, just comment out the WHERE clause and you will get everything.

-- Table column info
SELECT schema_name(t.schema_id) SchemaName
	,t.name TableName
	,c.name ColumnName
	,c.column_id
	,ct.name DataType
	,CASE CAST(c.max_length AS VARCHAR(50)) WHEN -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(50)) END AS max_length
	,c.precision
	,c.scale
	,c.collation_name Collation
	,c.is_nullable
	,c.is_computed
	,c.is_identity
	,c.is_rowguidcol
	,c.is_replicated
	,c.is_merge_published
	,c.is_sparse
FROM sys.tables t
JOIN sys.columns c on (t.object_id = c.object_id)
JOIN sys.types ct on (ct.user_type_id = c.user_type_id)
WHERE schema_name(t.schema_id) = 'Sales'
AND t.name = 'Orders'
ORDER BY schema_name(t.schema_id), t.name, c.column_id

This returns more info than sp_help.  I also found that in sp_help for the table info there are a couple of odd columns named TrimTrailingBlanks and FixedLenNullInSource. 

TrimTrailingBlanks is apparently populated with NO if ANSI_PADDING is set to ON.

FixedLenNullInSource is something that is leftover from possibly as far back as Pre-SQL 2000 or Sybase days.  In researching it seems that this used to be hardcoded with YES if certain data type columns were nullable.  It looks like this has gone by the wayside but it is still part of the sp_help result set.  So, I think I’ll leave these 2 columns out of my results.

After the column info in sp_help there is also the information about column defaults and primary, foreign and unique key constraints listed.  You can also get this information by using sp_helpconstraint.  (https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpconstraint-transact-sql?view=sql-server-2017 )

EXEC sp_helpconstraint 'Sales.Orders'

However, while this data is great I am wanting to get individual result sets.  The below three queries are designed to bring back info much like shown in sp_helpconstraint.  You can see how this makes significant progress towards my final goal of gathering information that will allow me to script tables.

-- defaults
SELECT SCHEMA_NAME(t.schema_id) SchemaName
	,t.name TableName 
	,c.name ConstraintName
	,c.definition ConstraintDefinition
	,col.name ColumnName 
FROM sys.default_constraints c 
JOIN sys.columns col ON (col.default_object_id = c.object_id) 
JOIN sys.tables t ON (t.object_id = c.parent_object_id)
WHERE SCHEMA_NAME(t.schema_id) = 'Sales'
AND t.name = 'Orders'
ORDER BY SchemaName, TableName, ConstraintName

-- foreign keys
SELECT OBJECT_SCHEMA_NAME(f.parent_object_id) AS FKSchemaName
	,OBJECT_NAME(f.parent_object_id) FKBaseTable
	,CASE f.is_not_trusted WHEN 0 THEN 'WITH CHECK' WHEN 1 THEN 'NOCHECK' END AS CheckOrNoCheck
	,f.name AS ConstraintName
	,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS FKBaseColumn
	,OBJECT_SCHEMA_NAME(f.referenced_object_id) AS PKRefdSchemaName
	,OBJECT_NAME (f.referenced_object_id) AS PKRefdBaseTable
	,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKRefdBaseColumn
	,f.delete_referential_action_desc
	,f.update_referential_action_desc
FROM sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc ON (f.object_id = fc.constraint_object_id)
WHERE OBJECT_SCHEMA_NAME(f.parent_object_id) = 'Sales'
AND OBJECT_NAME (f.parent_object_id) = 'Orders'
ORDER BY FKSchemaName, FKBaseTable, ConstraintName

-- primary or unique keys
SELECT SCHEMA_NAME(t.schema_id) SchemaName
	,t.name AS TableName
	,inx.name AS IndexName
	,CASE WHEN inx.is_unique_constraint = 1 THEN 'UNIQUE' ELSE '' END AS [Unique]
	,CASE WHEN inx.is_primary_key = 1 THEN 'PRIMARY' ELSE '' END AS PrimaryKey
	,inx.type_desc AS IndexType
	,CASE WHEN inx.is_padded = 1 THEN 'ON' ELSE 'OFF' END AS PadIndex
	,CASE WHEN inx.allow_page_locks=1 THEN 'ON' ELSE 'OFF' END AS AllowPageLocks
	,CASE WHEN inx.allow_row_locks=1 THEN 'ON' ELSE 'OFF' END AS AllowRowLocks
	,CASE WHEN INDEXPROPERTY(t.object_id, inx.name, 'IsStatistics') = 1 THEN 'ON' ELSE 'OFF' END AS StatisticsNoRecompute
	,CASE WHEN inx.ignore_dup_key=1 THEN 'ON' ELSE 'OFF' END AS IgnoreDupKey
	,inx.fill_factor AS [FillFactor]
	,FILEGROUP_NAME(inx.data_space_id) AS FileGroupName
FROM sys.tables t 
JOIN sys.indexes inx ON (t.object_id=inx.object_id)
WHERE  (inx.is_primary_key = 1 OR inx.is_unique_constraint = 1)
AND inx.type > 0
AND t.is_ms_shipped=0
AND SCHEMA_NAME(t.schema_id) = 'Sales'
AND t.name = 'Orders'
ORDER BY SchemaName, TableName, IndexName

So what now?  Well….we are beginning to get enough information to start building a basic script to create table-key-default-constraint statements.  My next installment will be to cover getting a little more information needed to script a table, and I will be looking for non-clustered indexes and table triggers.  Until then…. Happy Querying!!

In my examples I have used the Wide World Importers database.  This sample database replaces the old AdventureWorks databases.  You can download the Wide World Importers OLTP and Data Warehouse databases here:

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

Find out more about them here: https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-what-is?view=sql-server-2017

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com.  We will be happy to help!  Leave a comment and feel free to track back to us.  We love to talk tech with anyone in our SQL family!