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

Denali Enhancement–Duplicate Key Error Message

drsql — Thu, 14 Jul 2011 05:12:35 GMT

When I was editing my chapter on implementing a database, I noticed a really nice improvement in the error message I had from the previous edition of the book. Instead of just telling me that there was a value in my modification statement that duplicated an existing value (or multiple values affected by the statement), it told me the duplicated value.

To demo, I created the following quickie table in tempdb.

USE tempdb
GO

--drop the object if it initially existed
if object_id('test.testErrorMessage') IS NOT null
DROP TABLE test.testErrorMessage
IF schema_id('test') IS NOT NULL
DROP SCHEMA test
go

CREATE SCHEMA test
GO
CREATE TABLE test.testErrorMessage
(
    testErrorMessageId INT NOT NULL
        CONSTRAINT PKtestErrorMessage PRIMARY KEY,
    otherColumn varchar(10) NOT NULL
        CONSTRAINT AKtestErrorMessage UNIQUE (otherColumn),
)
GO
INSERT INTO test.testErrorMessage (testErrorMessageId, otherColumn)
VALUES (1,'First')
GO

Then, inserting a duplicate row for the primary key value:

INSERT INTO test.testErrorMessage (testErrorMessageId, otherColumn)
VALUES (1,'First')
GO

And on 2008 R2, I get:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'.

Now on Denali CTP3, you get a little bit more:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'. The duplicate key value is (1).

Then, to show the same thing for the UNIQUE CONSTRAINT:

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (2,'First')

On 2008 R2, you get the following

Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'.

And again on Denali CTP3:

Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'. The duplicate key value is (First).

You can see if you duplicate > 1 value, it gives you one of the items. It might be better if the message didn’t imply that it was the only duplicate value, but hey, it is a great improvement. If you think it ought to be tweaked to say “A duplicated key value is (…) or something, click here)

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (5,'Third'),(6,'Third'),(3,'Second'),(4,'Second')

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'. The duplicate key value is (Third).

It works with indexes also:

ALTER TABLE test.testErrorMessage
DROP CONSTRAINT AKtestErrorMessage

CREATE UNIQUE INDEX UXtestErrorMessage ON test.testErrorMessage(otherColumn)

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (5,'Third'),(6,'Third'),(3,'Second'),(4,'Second')

You get the following:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'test.testErrorMessage' with unique index 'UXtestErrorMessage'. The duplicate key value is (Third).

Much nicer!

Search results matching tags 'T-SQL' and 'Constraints'

Utility Queries–Column Metadata

Denali Enhancement–Duplicate Key Error Message