Search results matching tags 't-sql' and 'Utility'

Utility Queries–Structure of Tables with Identity Column

drsql — Sun, 02 Sep 2012 20:05:00 GMT

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. 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.

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 presentation page), 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.

In the future (which will probably be after PASS, since I have a lot of prep 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:

Tables with no primary key – Very common scenario, no idea about uniqueness, or sometimes that identity property alone makes the table an adequate table.
Tables with no identity column – Abolutely nothing wrong with this scenario, as the pattern of using an identity based primary key is just a choice\preference. However, if you you expect all of your tables to have identity columns, running this query can show you where you are wrong. 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.
Tables with identity column and PK, identity column in AK – This query is interesting for looking at other people’s databases sometimes. 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 “interesting” cases.
Tables with an identity based column in the primary key along with other columns – In this case, the key columns are illogical. The identity value should always be unique and be a sufficient surrogate key on it's own. 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.

I can’t say that this is “wrong” 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.
Tables with a single column identity based primary key but no alternate key. – This is the classic ‘bad’ use of surrogate key abuse. Just drop a surrogate key on the table and viola!, uniqueness. If you can’t see why this wouldn’t be the desirable case, it is like the previous case, except the only uniqueness criteria is a monotonically increasing value.

You can download the code directly from here or you can see all my downloadable queries on my downloadable package page: DownloadablePackages.

The queries:

--Tables with no primary key

SELECT schemas.name + '.' + tables.name AS tableName
FROM    sys.tables
          JOIN sys.schemas
             ON tables.schema_id = schemas.schema_id
WHERE   tables.type_desc = 'USER_TABLE'
        --no PK key constraint exists
    AND NOT EXISTS ( SELECT *
                        FROM   sys.key_constraints
                        WHERE key_constraints.type = 'PK'
                            AND key_constraints.parent_object_id = tables.object_id )

--Tables with no identity column

SELECT schemas.name + '.' + tables.name AS tableName
FROM    sys.tables
           JOIN sys.schemas
               ON tables.schema_id = schemas.schema_id
WHERE   tables.type_desc = 'USER_TABLE'
--no column in the table has the identity property
    AND NOT EXISTS ( SELECT *
                     FROM   sys.columns
                     WHERE tables.object_id = columns.object_id
                       AND is_identity = 1 )

--Tables with identity column and PK, identity column in AK

SELECT schemas.name + '.' + tables.name AS tableName
FROM   sys.tables
        JOIN sys.schemas
            ON tables.schema_id = schemas.schema_id
WHERE tables.type_desc = 'USER_TABLE'
        -- table does have identity column
AND   EXISTS (    SELECT *
                    FROM   sys.columns
                    WHERE tables.object_id = columns.object_id
                        AND is_identity = 1 )
        -- table does have primary key
AND   EXISTS (    SELECT *
                    FROM   sys.key_constraints
                    WHERE key_constraints.type = 'PK'
                      AND key_constraints.parent_object_id = tables.object_id )
        -- but it is not the PK
AND   EXISTS (    SELECT *
                    FROM   sys.key_constraints
                        JOIN sys.index_columns
                            ON index_columns.object_id = key_constraints.parent_object_id
                                AND index_columns.index_id = key_constraints.unique_index_id
                        JOIN sys.columns
                            ON columns.object_id = index_columns.object_id
                                AND columns.column_id = index_columns.column_id
                    WHERE key_constraints.type = 'UQ'
                        AND key_constraints.parent_object_id = tables.object_id
                        AND columns.is_identity = 1 )

--Tables with an identity based column in the primary key along with other columns

SELECT schemas.name + '.' + tables.name AS tableName
FROM   sys.tables
        JOIN sys.schemas
            ON tables.schema_id = schemas.schema_id
WHERE tables.type_desc = 'USER_TABLE'
        -- table does have identity column
AND   EXISTS ( SELECT *
                 FROM   sys.columns
                 WHERE tables.object_id = columns.object_id
                   AND is_identity = 1 )
        --any PK has identity column
AND   EXISTS( SELECT *
                FROM    sys.key_constraints
                           JOIN sys.index_columns
                                ON index_columns.object_id = key_constraints.parent_object_id
                                   AND index_columns.index_id = key_constraints.unique_index_id
                           JOIN sys.columns
                                ON columns.object_id = index_columns.object_id
                                   AND columns.column_id = index_columns.column_id
                WHERE    key_constraints.type = 'PK'
                  AND    key_constraints.parent_object_id = tables.object_id
                  AND    columns.is_identity = 1 )
    --and there are > 1 columns in the PK constraint
    AND ( SELECT COUNT(*)
           FROM    sys.key_constraints
                      JOIN sys.index_columns
                          ON index_columns.object_id = key_constraints.parent_object_id
                             AND index_columns.index_id = key_constraints.unique_index_id
            WHERE   key_constraints.type = 'PK'
              AND   key_constraints.parent_object_id = tables.object_id
        ) > 1

--Tables with a single column identity based primary key but no alternate key

SELECT schemas.name + '.' + tables.name AS tableName
FROM sys.tables
         JOIN sys.schemas
             ON tables.schema_id = schemas.schema_id
WHERE tables.type_desc = 'USER_TABLE'
        --a PK key constraint exists
AND   EXISTS ( SELECT *
                 FROM   sys.key_constraints
                 WHERE key_constraints.type = 'PK'
                   AND key_constraints.parent_object_id = tables.object_id )
    --any PK only has identity column
AND ( SELECT COUNT(*)
        FROM   sys.key_constraints
                  JOIN sys.index_columns
                      ON index_columns.object_id = key_constraints.parent_object_id
                         AND index_columns.index_id = key_constraints.unique_index_id
                  JOIN sys.columns
                      ON columns.object_id = index_columns.object_id
                         AND columns.column_id = index_columns.column_id
        WHERE key_constraints.type = 'PK'
          AND key_constraints.parent_object_id = tables.object_id
          AND columns.is_identity = 0
        ) = 0 --must have > 0 columns in pkey, can only have 1 identity column

--but no Unique Constraint Exists
AND NOT EXISTS ( SELECT *
                   FROM   sys.key_constraints
                   WHERE key_constraints.type = 'UQ'
                     AND key_constraints.parent_object_id = tables.object_id )

--Test Cases

--The following are some sample tables that can be built to test these queries. If you have other ideas
--for cases (or find errors, email louis@drsql.org)

IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.NoPrimaryKey'))
        DROP TABLE dbo.NoPrimaryKey;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.NoIdentityColumn'))
        DROP TABLE dbo.NoIdentityColumn;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.IdentityButNotInPkey'))
        DROP TABLE dbo.IdentityButNotInPkey;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.TooManyColumnsInPkey'))
        DROP TABLE dbo.TooManyColumnsInPkey;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.MultipleColumnsInPkeyOk'))
        DROP TABLE dbo.MultipleColumnsInPkeyOk;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.NoAlternateKey'))
        DROP TABLE dbo.NoAlternateKey;
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = object_id('dbo.IdentityInAlternateKey'))
        DROP TABLE dbo.IdentityInAlternateKey;

--very common scenario, assuming identity makes the table great
CREATE TABLE NoPrimaryKey
(
NoPrimaryKeyId int not null identity,
AnotherColumnId int not null
)
go

--absolutely nothing wrong with this scenario, unless you expect all of your
--tables to have identity columns, of course...
CREATE TABLE NoIdentityColumn
(
NoIdentityColumnId int primary key,
AnotherColumnId int not null
)
go

--absolutely nothing wrong with this scenario either, as this could be desired.
--usually it is some form of mistake in a database using surrogate keys though
CREATE TABLE IdentityButNotInPkey
(
IdentityButNotInPkeyId int primary key,
AnotherColumnId int identity not null
)
go

--absolutely nothing wrong with this scenario either, as this could be desired.
--usually it is some form of mistake in a database using surrogate keys though
CREATE TABLE IdentityInAlternateKey
(
IdentityInAlternateKeyId int primary key,
AnotherColumnId int identity not null unique
)
go

--In this case, the key columns are illogical. The identity value should always be unique and
--be a sufficient primary surrogate key. I definitely want to know why this is built this
--way. Sometimes people with use this for an invoice line item and make the pk the
--invoiceId and an identity value like invoiceLineItemId. I generally prefer the surrogate key
--to stand alone and have the multi-part key to be something that makes sense for the user
CREATE TABLE TooManyColumnsInSurrogatePkey
(
    TooManyColumnsInPkeyId int identity,
    AnotherColumnId int,
    primary key (TooManyColumnsInPkeyId,AnotherColumnId)
)
go

CREATE TABLE MultipleColumnsInPkeyOk
(
    TooManyColumnsInPkeyId int not null,
    AnotherColumnId int not null,
    primary key (TooManyColumnsInPkeyId,AnotherColumnId)
)
go

--this is my pet peeve, and something that should be avoided. You could end up having
--duplicate rows that are not logical.
CREATE TABLE NoAlternateKey
(
NoAlternateKeyId int not null identity primary key,
AnotherColumnThatShouldBeUnique int not null
)
go

Utility Queries–Column Metadata

drsql — Mon, 23 Apr 2012 03:57:00 GMT

Very often, I find myself wanting to query system metadata for columns. Some of the metadata is fairly easy to deal with, like if you want to find column names, just simply querying sys.columns is very straightforward. But if you want to query data types, it gets stickier. There are type types listed, one that is the declared type, and another for the base data type, which if you are using CREATE TYPE to create alias data types. So I started working on the query based on INFORMATION SCHEMA values, because it is a lot easier to work with. In my design book, I used that version (and will link this version in erratta/book updates.

But in the Books Online entry for the COLUMNS object (and others too), I discovered the following warning, a bit too late the change: “Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.” So I rewrote it to use the catalog views.

The query is fairly complex looking, but overall is pretty simple. I put comments through the code that explains what it does. I will note that I have tested the database using a case sensitive collation, but I haven’t tried it with a and CLR types. The query returns the following:

table_type – The type from sys.tables. Could be a user table, system table, user view, internal table
schema_name – The name of the schema of the table that the column is in
table_name – The name of the table
column_name – The name of the column
nullability – Whether that column is nullable, plus if the column is an identity column
declared_datatype – The data type as declared. For alias types (like sysname), this will be the name the user specified.
base_datatype – The data type as is implemented. For alias types this will be the base intrinsic type (like for sysname this will be nvarchar(128))
primary_key_column – A bit value, indicating that the column participates in a primary key constraint
column_id – The internal key of the column, which is used for ordering the columns
default_value – If there is a default constraint on the column, this will be the text of the declaration
column_check_constraint – If there is a check constraint that only deals with the one column, it will be considered a column check constraint. This will contain the text of the check constraint
table_check_constraint_reference – A bit value that indicates that there is one or more table level check constraints that reference the column

The query text follows. It is pretty unwieldy, so instead of the derived table you might want to consider building it into a view or even a procedure. I keep a SSMS project with all of my metadata queries that I maintain over time to call in any place I need them, so I use use this one ad-hoc. You can download the query from my website’s downloadable packages page here: http://www.drsql.org/Documents/ColumnMetadataQuery.sql

SELECT *
FROM    ( SELECT    REPLACE(LOWER(objects.type_desc), '_', ' ') AS table_type, schemas.name AS schema_name, objects.name AS table_name,
                    columns.name AS column_name, CASE WHEN columns.is_identity = 1 THEN 'IDENTITY NOT NULL'
                                                      WHEN columns.is_nullable = 1 THEN 'NULL'
                                                      ELSE 'NOT NULL'
                                                 END AS nullability,
                   --types that have a ascii character or binary length
                    CASE WHEN columns.is_computed = 1 THEN 'Computed'
                         WHEN types.name IN ( 'varchar', 'char', 'varbinary' ) THEN types.name +
                                         CASE WHEN columns.max_length = -1 THEN '(max)'
                                                ELSE '(' + CAST(columns.max_length AS VARCHAR(4)) + ')' END
                         --types that have an unicode character type that requires length to be halved
                         WHEN types.name IN ( 'nvarchar', 'nchar' ) THEN types.name +
                                          CASE WHEN columns.max_length = -1 THEN '(max)'
                                                ELSE '(' + CAST(columns.max_length / 2 AS VARCHAR(4)) + ')'
                                                                                     END
                          --types with a datetime precision
                         WHEN types.name IN ( 'time', 'datetime2', 'datetimeoffset' ) THEN types.name +
                                                                   '(' + CAST(columns.scale AS VARCHAR(4)) + ')'

                         --types with a precision/scale
                         WHEN types.name IN ( 'numeric', 'decimal' )
                         THEN types.name + '(' + CAST(columns.precision AS VARCHAR(4)) + ',' +
                                                                   CAST(columns.scale AS VARCHAR(4)) + ')'

                        --timestamp should be reported as rowversion
                         WHEN types.name = 'timestamp' THEN 'rowversion'
                         --and the rest. Note, float is declared with a bit length, but is
                         --represented as either float or real in types
                         ELSE types.name
                    END AS declared_datatype,

                   --types that have a ascii character or binary length
                    CASE WHEN baseType.name IN ( 'varchar', 'char', 'varbinary' )
                                  THEN baseType.name +
                                             CASE WHEN columns.max_length = -1 THEN '(max)'
                                                   ELSE '(' + CAST(columns.max_length AS VARCHAR(4)) + ')'
                                              END

                         --types that have an unicode character type that requires length to be halved
                         WHEN baseType.name IN ( 'nvarchar', 'nchar' )
                                  THEN baseType.name +
                                              CASE WHEN columns.max_length = -1 THEN '(max)'
                                                    ELSE '(' + CAST(columns.max_length / 2 AS VARCHAR(4)) + ')'
                                              END

                         --types with a datetime precision
                         WHEN baseType.name IN ( 'time', 'datetime2', 'datetimeoffset' )
                                   THEN baseType.name + '(' + CAST(columns.scale AS VARCHAR(4)) + ')'

                         --types with a precision/scale
                         WHEN baseType.name IN ( 'numeric', 'decimal' )
                         THEN baseType.name + '(' + CAST(columns.precision AS VARCHAR(4)) +
                                                 ',' + CAST(columns.scale AS VARCHAR(4)) + ')'

                         --timestamp should be reported as rowversion
                         WHEN baseType.name = 'timestamp' THEN 'rowversion'
                         --and the rest. Note, float is declared with a bit length, but is
                         --represented as either float or real in types
                         ELSE baseType.name
                    END AS base_datatype,
                    CASE WHEN EXISTS ( SELECT *
                                       FROM   sys.key_constraints
                                                 JOIN sys.indexes
                                                      ON key_constraints.parent_object_id = indexes.object_id
                                                          AND key_constraints.unique_index_id = indexes.index_id
                                                 JOIN sys.index_columns
                                                      ON index_columns.object_id = indexes.object_id
                                                          AND index_columns.index_id = indexes.index_id
                                       WHERE key_constraints.type = 'PK'
                                         AND columns.column_id = index_columns.column_id
                                         AND columns.OBJECT_ID = index_columns.OBJECT_ID )
                                  THEN 1
                          ELSE 0 END AS primary_key_column,
                    columns.column_id, default_constraints.definition AS default_value,
                    check_constraints.definition AS column_check_constraint,
                    CASE WHEN EXISTS ( SELECT   *
                                       FROM     sys.check_constraints AS cc
                                       WHERE    cc.parent_object_id = columns.OBJECT_ID
                                                AND cc.definition LIKE '%~[' + columns.name + '~]%' ESCAPE '~'
                                                AND cc.parent_column_id = 0 ) THEN 1
                         ELSE 0
                    END AS table_check_constraint_reference
          FROM      sys.columns
                    JOIN sys.types
                        ON columns.user_type_id = types.user_type_id
                    JOIN sys.types AS baseType
                        ON columns.system_type_id = baseType.system_type_id
                           AND baseType.user_type_id = baseType.system_type_id
                    JOIN sys.objects
                            JOIN sys.schemas
                                   ON schemas.schema_id = objects.schema_id
                        ON objects.object_id = columns.OBJECT_ID
                    LEFT OUTER JOIN sys.default_constraints
                        ON default_constraints.parent_object_id = columns.object_id
                              AND default_constraints.parent_column_id = columns.column_id
                    LEFT OUTER JOIN sys.check_constraints
                        ON check_constraints.parent_object_id = columns.object_id
                             AND check_constraints.parent_column_id = columns.column_id ) AS rows
WHERE   table_type = 'user table'
              AND schema_name LIKE '%'
              AND table_name LIKE '%'
              AND column_name LIKE '%'
              AND nullability LIKE '%'
              AND base_datatype LIKE '%'
              AND declared_datatype LIKE '%'
ORDER BY table_type, schema_name, table_name, column_id