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

By Lori Brown | Dependencies

Sep 14

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:

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.

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 )

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.

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!

>