{"id":82130,"date":"2012-09-02T16:05:00","date_gmt":"2012-09-02T16:05:00","guid":{"rendered":"https:\/\/www.webstaging.red-gate.com\/simple-talk\/?p=73433"},"modified":"2018-12-12T13:17:04","modified_gmt":"2018-12-12T13:17:04","slug":"utility-queries-structure-of-tables-with-identity-column","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/utility-queries-structure-of-tables-with-identity-column\/","title":{"rendered":"Utility Queries\u2013Structure of Tables with Identity Column"},"content":{"rendered":"<p><em>Edit: At the suggestion of a much knowledgable commenter who shall remain named Aaron, I changed from using schema_name() function to using sys.tables.&nbsp;When writing code that is expected to have reuse, it can be safer to use the tables rather than functions because the tables will work in the context of the database that is in the from clause, so if you changed the code to database1.sys.tables because you wanted the tables from database1, and you were executing the code in database2, the columns of the table would give you the answer you expected, but the functions would be context of database2.<\/em><\/p>\n<p>I have been doing a presentation on sequences of late (last planned version of that presentation was last week, but should be able to get the gist of things from the slides and the code posted here on my <a title=\"Presentation Page\" href=\"http:\/\/www.drsql.org\/Pages\/Presentations.aspx\">presentation page<\/a>), and as part of that process, I started writing some queries to interrogate the structure of tables. I started with tables using an identity column for some purpose because they are considerably easier to do than sequences, specifically because the limitations of identity columns make determining how they are used easier.<\/p>\n<p>In the future (which will probably be after PASS, since I have a <a href=\"https:\/\/www.webstaging.red-gate.com\/simple-talk\/uncategorized\/sqlpass-db-design-precon-preview\/\" target=\"_blank\">lot of prep<\/a> and 3 more presentations to do before PASS), I will start trying to discern the different cases where you might want to use a sequence and writing queries to make sure the table structures are as I desire. The queries presented here are really the first step in this direction, as in most cases I foresee a mixture of identity and sequence based surrogate keys even once people get to SQL Server 2012 as a typical set up. The queries I am presenting here will look for tables that meet certain conditions, including:<\/p>\n<ul>\n<li>Tables with no primary key \u2013 Very common scenario, no idea about uniqueness, or sometimes that identity property alone makes the table an adequate table.      <\/li>\n<li>Tables with no identity column \u2013 Abolutely nothing wrong with this scenario, as the pattern of using an identity based primary key is just a choice\\preference.&nbsp; However, if you you expect all of your tables to have identity columns, running this query can show you where you are wrong.&nbsp; I usually use this sort of query as part of a release, making sure that the tables I expected to have a surrogate actually do.      <\/li>\n<li>Tables with identity column and PK, identity column in AK \u2013 This query is interesting for looking at other people\u2019s databases sometimes.&nbsp; Not everyone uses the identity value as a surrogate primary key, and finding cases where it is in a non-key usage can help you find \u201cinteresting\u201d cases.      <\/li>\n<li>Tables with an identity based column in the primary key along with other columns \u2013 In this case, the key columns are illogical. The identity value should always be unique and be a sufficient surrogate key on it&#8217;s own.&nbsp; By putting other columns in the key, you end up with a false sense of uniqueness. Ideally, you want your tables to have at least one key where all of the values are created outside of SQL Server. Sometimes people with use this for an invoice line item and make the pk the invoiceId and an identity value like invoiceLineItemId.\n<p>I can\u2019t say that this is \u201cwrong\u201d but if the only key includes a system generated value, it means that you can have duplicated data along with the system generated value. So you need to monitor the data more carefully.       <\/li>\n<li>Tables with a single column identity based primary key but no alternate key. \u2013 This is the classic \u2018bad\u2019 use of surrogate key abuse. Just drop a surrogate key on the table and viola!, uniqueness. If you can\u2019t see why this wouldn\u2019t be the desirable case, it is like the previous case, except the only uniqueness criteria is a monotonically increasing value. <\/li>\n<\/ul>\n<p>You can download the code directly from <a href=\"http:\/\/www.drsql.org\/Documents\/IdentityTableQueries.sql\" target=\"_blank\">here<\/a>&nbsp; or you can see all my downloadable queries on my downloadable package page: <a title=\"http:\/\/www.drsql.org\/Pages\/DownloadablePackages.aspx\" href=\"http:\/\/www.drsql.org\/Pages\/DownloadablePackages.aspx\" target=\"_blank\">DownloadablePackages<\/a>. <\/p>\n<p>The queries:<\/p>\n<p><strong>&#8211;Tables with no primary key<\/strong><\/p>\n<p>SELECT&nbsp; schemas.name + &#8216;.&#8217; + tables.name AS tableName<br \/>FROM&nbsp;&nbsp;&nbsp; sys.tables<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; JOIN sys.schemas<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;ON tables.schema_id = schemas.schema_id<br \/>WHERE&nbsp;&nbsp; tables.type_desc = &#8216;USER_TABLE&#8217;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#8211;no PK key constraint exists<br \/>&nbsp;&nbsp;&nbsp; AND NOT EXISTS ( SELECT *<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.key_constraints<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; key_constraints.type = &#8216;PK&#8217;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND key_constraints.parent_object_id = tables.object_id ) <\/p>\n<p><strong>&#8211;Tables with no identity column<\/strong><\/p>\n<p>SELECT&nbsp; schemas.name + &#8216;.&#8217; + tables.name AS tableName<br \/>FROM&nbsp;&nbsp;&nbsp; sys.tables<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; JOIN sys.schemas<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ON tables.schema_id = schemas.schema_id<br \/>WHERE&nbsp;&nbsp; tables.type_desc = &#8216;USER_TABLE&#8217;<br \/>&#8211;no column in the table has the identity property<br \/>&nbsp;&nbsp;&nbsp; AND NOT EXISTS ( SELECT *<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.columns<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; tables.object_id = columns.object_id<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND is_identity = 1 )<\/p>\n<p><strong>&#8211;Tables with identity column and PK, identity column in AK<\/strong><\/p>\n<p>SELECT  schemas.name + &#8216;.&#8217; + tables.name AS tableName<br \/>FROM&nbsp;&nbsp;&nbsp;sys.tables<br \/>          &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.schemas<br \/>              &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON tables.schema_id = schemas.schema_id<br \/>WHERE   tables.type_desc = &#8216;USER_TABLE&#8217;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#8212; table does have identity column&nbsp; <br \/>&nbsp; AND&nbsp;&nbsp; EXISTS (&nbsp;&nbsp;&nbsp; SELECT *       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.columns       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; tables.object_id = columns.object_id       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND is_identity = 1 )&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#8212; table does have primary key&nbsp; <br \/>&nbsp; AND&nbsp;&nbsp; EXISTS (&nbsp;&nbsp;&nbsp; SELECT *       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.key_constraints       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; key_constraints.type = &#8216;PK&#8217;       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND key_constraints.parent_object_id = tables.object_id )       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#8212; but it is not the PK&nbsp; <br \/>&nbsp; AND&nbsp;&nbsp; EXISTS (&nbsp;&nbsp;&nbsp; SELECT *       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.key_constraints       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.index_columns       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON index_columns.object_id = key_constraints.parent_object_id       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND index_columns.index_id = key_constraints.unique_index_id       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.columns       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON columns.object_id = index_columns.object_id       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND columns.column_id = index_columns.column_id       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; key_constraints.type = &#8216;UQ&#8217;       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND key_constraints.parent_object_id = tables.object_id       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND columns.is_identity = 1 ) <\/p>\n<p><strong>&#8211;Tables with an identity based column in the primary key along with other columns<\/strong><\/p>\n<p>SELECT  schemas.name + &#8216;.&#8217; + tables.name AS tableName<br \/>FROM&nbsp;&nbsp;&nbsp;sys.tables<br \/>          &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;JOIN sys.schemas<br \/>              &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON tables.schema_id = schemas.schema_id<br \/>WHERE   tables.type_desc = &#8216;USER_TABLE&#8217;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#8212; table does have identity column      <br \/>&nbsp; AND&nbsp;&nbsp; EXISTS ( SELECT *      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.columns      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; tables.object_id = columns.object_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND is_identity = 1 )      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#8211;any PK has identity column      <br \/>&nbsp; AND&nbsp;&nbsp; EXISTS( SELECT&nbsp; *      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp;&nbsp; sys.key_constraints      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.index_columns      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON index_columns.object_id = key_constraints.parent_object_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND index_columns.index_id = key_constraints.unique_index_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.columns      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON columns.object_id = index_columns.object_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND columns.column_id = index_columns.column_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp;&nbsp;&nbsp; key_constraints.type = &#8216;PK&#8217;      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND&nbsp;&nbsp;&nbsp; key_constraints.parent_object_id = tables.object_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND&nbsp;&nbsp;&nbsp; columns.is_identity = 1 )       <br \/>&nbsp;&nbsp;&nbsp; &#8211;and there are &gt; 1 columns in the PK constraint      <br \/>&nbsp;&nbsp;&nbsp; AND (&nbsp; SELECT&nbsp; COUNT(*)      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp;&nbsp; sys.key_constraints      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.index_columns      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON index_columns.object_id = key_constraints.parent_object_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND index_columns.index_id = key_constraints.unique_index_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp;&nbsp; key_constraints.type = &#8216;PK&#8217;      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND&nbsp;&nbsp; key_constraints.parent_object_id = tables.object_id      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) &gt; 1<\/p>\n<p><\/p>\n<p><strong>&#8211;Tables with a single column identity based primary key but no alternate key<\/strong><\/p>\n<p>SELECT  schemas.name + &#8216;.&#8217; + tables.name AS tableName<br \/>FROM    sys.tables<br \/>          &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.schemas<br \/>              &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON tables.schema_id = schemas.schema_id<br \/>WHERE   tables.type_desc = &#8216;USER_TABLE&#8217;<br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#8211;a PK key constraint exists&nbsp; <br \/>&nbsp; AND&nbsp;&nbsp; EXISTS ( SELECT *&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.key_constraints&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; key_constraints.type = &#8216;PK&#8217;&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND key_constraints.parent_object_id = tables.object_id )       <br \/>&nbsp;&nbsp;&nbsp; &#8211;any PK only has identity column&nbsp; <br \/>&nbsp; AND ( SELECT COUNT(*)&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.key_constraints&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.index_columns&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON index_columns.object_id = key_constraints.parent_object_id&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND index_columns.index_id = key_constraints.unique_index_id&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JOIN sys.columns&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON columns.object_id = index_columns.object_id&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND columns.column_id = index_columns.column_id&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; key_constraints.type = &#8216;PK&#8217;&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND&nbsp; key_constraints.parent_object_id = tables.object_id&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND columns.is_identity = 0       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) = 0 &#8211;must have &gt; 0 columns in pkey, can only have 1 identity column&nbsp; <\/p>\n<p>&nbsp; &#8211;but no Unique Constraint Exists&nbsp; <br \/>&nbsp; AND NOT EXISTS ( SELECT *&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM&nbsp;&nbsp; sys.key_constraints&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; key_constraints.type = &#8216;UQ&#8217;&nbsp; <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND key_constraints.parent_object_id = tables.object_id )&nbsp;&nbsp; <\/p>\n<p><strong>&#8211;Test Cases <\/strong><\/p>\n<p>&#8211;The following are some sample tables that can be built to test these queries. If you have other ideas   <br \/>&#8211;for cases (or find errors, email <a href=\"http:\/\/mailto:louis@drsql.org\">louis@drsql.org<\/a>)<\/p>\n<p>IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(&#8216;dbo.NoPrimaryKey&#8217;))      <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP TABLE dbo.NoPrimaryKey;      <br \/>IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(&#8216;dbo.NoIdentityColumn&#8217;))       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP TABLE dbo.NoIdentityColumn;      <br \/>IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(&#8216;dbo.IdentityButNotInPkey&#8217;))       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP TABLE dbo.IdentityButNotInPkey;      <br \/>IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(&#8216;dbo.TooManyColumnsInPkey&#8217;))       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP TABLE dbo.TooManyColumnsInPkey;      <br \/>IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(&#8216;dbo.MultipleColumnsInPkeyOk&#8217;))       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP TABLE dbo.MultipleColumnsInPkeyOk;      <br \/>IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(&#8216;dbo.NoAlternateKey&#8217;))       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP TABLE dbo.NoAlternateKey;      <br \/>IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id(&#8216;dbo.IdentityInAlternateKey&#8217;))       <br \/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DROP TABLE dbo.IdentityInAlternateKey;<\/p>\n<p>&#8211;very common scenario, assuming identity makes the table great     <br \/>CREATE TABLE NoPrimaryKey      <br \/>(      <br \/>&nbsp;&nbsp;&nbsp; NoPrimaryKeyId int not null identity,      <br \/>&nbsp;&nbsp;&nbsp; AnotherColumnId int not null       <br \/>)      <br \/>go<\/p>\n<p>&#8211;absolutely nothing wrong with this scenario, unless you expect all of your     <br \/>&#8211;tables to have identity columns, of course&#8230;      <br \/>CREATE TABLE NoIdentityColumn      <br \/>(      <br \/>&nbsp;&nbsp;&nbsp; NoIdentityColumnId int primary key,      <br \/>&nbsp;&nbsp;&nbsp; AnotherColumnId int not null       <br \/>)      <br \/>go<\/p>\n<p>&#8211;absolutely nothing wrong with this scenario either, as this could be desired.      <br \/>&#8211;usually it is some form of mistake in a database using surrogate keys though      <br \/>CREATE TABLE IdentityButNotInPkey      <br \/>(      <br \/>&nbsp;&nbsp;&nbsp; IdentityButNotInPkeyId int primary key,      <br \/>&nbsp;&nbsp;&nbsp; AnotherColumnId int identity not null       <br \/>)      <br \/>go<\/p>\n<p>&#8211;absolutely nothing wrong with this scenario either, as this could be desired.      <br \/>&#8211;usually it is some form of mistake in a database using surrogate keys though      <br \/>CREATE TABLE IdentityInAlternateKey      <br \/>(      <br \/>&nbsp;&nbsp;&nbsp; IdentityInAlternateKeyId int primary key,      <br \/>&nbsp;&nbsp;&nbsp; AnotherColumnId int identity not null unique      <br \/>)      <br \/>go<\/p>\n<p>&#8211;In this case, the key columns are illogical. The identity value should always be unique and      <br \/>&#8211;be a sufficient primary surrogate key. I definitely want to know why this is built this      <br \/>&#8211;way.&nbsp; Sometimes people with use this for an invoice line item and make the pk the       <br \/>&#8211;invoiceId and an identity value like invoiceLineItemId. I generally prefer the surrogate key      <br \/>&#8211;to stand alone and have the multi-part key to be something that makes sense for the user      <br \/>CREATE TABLE TooManyColumnsInSurrogatePkey      <br \/>(      <br \/>&nbsp;&nbsp;&nbsp; TooManyColumnsInPkeyId int identity,      <br \/>&nbsp;&nbsp;&nbsp; AnotherColumnId int,      <br \/>&nbsp;&nbsp;&nbsp; primary key (TooManyColumnsInPkeyId,AnotherColumnId)      <br \/>)      <br \/>go<\/p>\n<p>CREATE TABLE MultipleColumnsInPkeyOk     <br \/>(      <br \/>&nbsp;&nbsp;&nbsp; TooManyColumnsInPkeyId int not null,      <br \/>&nbsp;&nbsp;&nbsp; AnotherColumnId int not null,      <br \/>&nbsp;&nbsp;&nbsp; primary key (TooManyColumnsInPkeyId,AnotherColumnId)      <br \/>)      <br \/>go<\/p>\n<p>&#8211;this is my pet peeve, and something that should be avoided. You could end up having     <br \/>&#8211;duplicate rows that are not logical.      <br \/>CREATE TABLE NoAlternateKey      <br \/>(      <br \/>&nbsp;&nbsp;&nbsp; NoAlternateKeyId int not null identity primary key,      <br \/>&nbsp;&nbsp;&nbsp; AnotherColumnThatShouldBeUnique int not null      <br \/>)      <br \/>go<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Edit: At the suggestion of a much knowledgable commenter who shall remain named Aaron, I changed from using schema_name() function to using sys.tables.&nbsp;When writing code that is expected to have reuse, it can be safer to use the tables rather than functions because the tables will work in the context of the database that is&#8230;&hellip;<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-82130","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=82130"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82130\/revisions"}],"predecessor-version":[{"id":82309,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/82130\/revisions\/82309"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=82130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=82130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=82130"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=82130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}