Super Quick Table Meta Data

  • ola

    thought i saw that code somewhere, was thinking of until ... your name dawned on me 😀

  • By default, we use a BINARY collation on my team. The TableInfo code has a few character case problems, which were easy to find and fix. Same goes for the assignment of default values to local variables; easy enough to fix in the script.

    Don't make the assumption that you can prefix the proc name with "sp_" , create it in master, and have it work in any User database. It won't necessarily generate any errors, but it also won't generate any output.

    Thanks for the example, it presents some food for thought. 🙂

  • Hi Rich,

    This seems to scare some people but it works very nicely in my experience:

    By setting stored procedures as being a "system object", you can create them in the master database, prefix them with "sp_", and have them run in any database "natively".

    The proc for doing this is "sp_ms_marksystemobject" - it works in 2000 and 2005, I have not tested in 2008.

    Hope this helps!

    Tao

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • But surprisingly, not triggers.

  • Just change the lines that read:

    declare @DBName varchar (20) = PARSENAME (@TableName, 3)

    to

    declare @DBName varchar (20)

    set @DBName = PARSENAME (@TableName, 3)

    SQLServer 2005 just requires 2 rows instead of 1 row.

  • [font="Verdana"]Here's a modified form that will work with SQL Server 2005.

    Note that I have not reviewed the code other than to change the issue of assignment during variable declaration (a SQL Server 2008 feature.)

    [/font]

  • Thank you, Bruce W Cassidy, for posting the SQL 2K5 version, although GSquared had already uploaded one in an earlier post.

    Tommy Balle, and randal.schmidt, did you come right with the 2K5 versions provided by the guys above? I'm sorry that I didn't test it for compatibility with SQL Server 2005, but in my own defense, I did mention this right at the end of the article.

    Rich Holt, can you point out the character case problems that you mentioned?

    hardtarget_x wrote:

    >You know a feature that would be neat to add, if you left the table empty or put in a %, and then typed in a column name... Then return all

    >of the tables that hold that specific column name.

    Yes I like that. Then you could get a quick overview of the columns you are getting back from multiple tables and compare their data types etc.

    Concerning the sp_help proc, yes I should have probably mentioned it. It definitely returns more info than TableInfo does. However, the TableInfo proc, when not using the extended flag, returns a compact yet reasonably comprehensive single resultset. It very clearly shows up which columns have indexes and foreign keys, and to which tables and fields those link to. When using it, most info relevant to a column is right there in the single row for that column, and you don't have to scan through the multiple resultsets as you would have to when using sp_help. Mix and use them as you want, but personally I very rarely have to revert back to sp_help to get what I'm looking for.

    As Tony Webster said, if we don't like, or have a use for what comes out of the box, we can write something that better suits our needs / method of working. We can change the box 🙂

  • Hey Jacques

    Now it works, thanks for a fine job.

    regards Tommy

  • I got it to work on Sql2005 with minor changes

    Here's the code

    if exists (select * from sys.objects where object_id = OBJECT_ID(N'[dbo].[TableInfo]') and type in (N'P', N'PC'))

    drop procedure [dbo].[TableInfo]

    go

    set ansi_nulls on

    go

    set quoted_identifier on

    go

    create procedure [dbo].[TableInfo]

    @TableNam varchar (700), -- Can include schema/owner.

    @FieldNameFilter varchar (700) = null, -- Compared with the like operator.

    @Extended bit = 0 -- For showing extra tableInfo such as triggers

    as

    --*****************************************

    -- Author: Jacques Bosch

    -- Last Modified: 25 Feb 2009

    --*****************************************

    set concat_null_yields_null off

    declare @DBName varchar (20)

    Set @DBName = PARSENAME (@TableNam, 3)--Stuart changed

    declare @TableSchema varchar (20)

    Set @TableSchema= PARSENAME (@TableNam, 2)--Stuart changed

    Declare @TableName varchar (700)

    set @TableName = PARSENAME (@TableNam, 1)--Stuart changed

    if @DBName is not null

    and @DBName != DB_NAME()

    begin

    print 'Cannot run this on DB ''' + @DBName + '''. Must be run on current DB.'

    return;

    end

    -- Set up some values for displaying the results.

    declare @Y varchar (10)

    Set @Y = ' y' -- Spacing is for nicer look.

    declare @Empty varchar (1)

    Set @Empty = ''; --Stuart changed. Added the ';'

    -- First get all the existing constraint tableInfo that is needed for the table.

    -- We are querying into a common table expression.

    with tableConstraints

    as

    (

    select distinct

    tbl.TABLE_NAME,

    kcu.TABLE_SCHEMA,

    col.COLUMN_NAME,

    tc.CONSTRAINT_NAME,

    tc.CONSTRAINT_TYPE,

    tc.TABLE_SCHEMA + '.' + OBJECT_NAME(sfk.RKEYID) as FK_Table,

    COL_NAME(RKEYID, RKEY) as FK_Field,

    OBJECTPROPERTY(sfk.CONSTID, 'CNSTISDISABLED') as FK_Disabled

    from

    Information_Schema.Tables tbl

    inner join

    Information_Schema.Columns col

    on col.TABLE_NAME = tbl.TABLE_NAME

    inner join

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

    on kcu.TABLE_NAME = col.TABLE_NAME

    and kcu.COLUMN_NAME = col.COLUMN_NAME

    inner join

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

    on tc.TABLE_NAME = kcu.TABLE_NAME

    and tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME

    left outer join

    SYSFOREIGNKEYS sfk

    on OBJECT_NAME(sfk.CONSTID) = kcu.CONSTRAINT_NAME

    and OBJECT_NAME(sfk.FKEYID) = kcu.TABLE_NAME

    and COL_NAME(FKEYID, FKEY) = kcu.COLUMN_NAME

    where

    kcu.TABLE_NAME = @TableName

    and

    (

    @TableSchema is null

    or kcu.TABLE_SCHEMA = @TableSchema

    )

    ),

    otherConstraint

    as

    (

    -- List the first of other tableConstraints, such as a unique constraint.

    select top 1

    cons.TABLE_SCHEMA,

    cons.TABLE_NAME,

    cons.COLUMN_NAME,

    cons.CONSTRAINT_TYPE

    from

    INFORMATION_SCHEMA.Columns c

    inner join

    tableConstraints cons

    on cons.TABLE_SCHEMA = c.TABLE_SCHEMA

    and cons.TABLE_NAME = c.TABLE_NAME

    and cons.COLUMN_NAME = c.COLUMN_NAME

    and cons.CONSTRAINT_TYPE not in ('PRIMARY KEY', 'FOREIGN KEY')

    ),

    tableIndexes

    as

    (

    select

    OBJECT_SCHEMA_NAME (sc.object_id) as TABLE_SCHEMA,

    OBJECT_NAME (sc.object_id) as TABLE_NAME,

    sc.name as COLIMN_NAME,

    -- Because we can have more than one index per column, this will cause multiple

    -- rows per column in our results set. Hence, we aggregate to prevent this, showing

    -- only one index per column.

    MIN (i.type_desc) as type_desc, -- Prefers clusterred over nonclusterred if both exist.

    -- We cast to tinyint because MIN and MAX don't work with bit.

    MAX (CAST (i.is_unique as tinyint)) as is_unique,-- Prefers unique indexes over non-unique.

    MIN (CAST (i.is_disabled as tinyint)) as is_disabled, -- Prefers enabled indexes over disabled.

    MAX (CAST (ic.is_descending_key as tinyint)) as is_descending_key -- Prefers descending indexes over ascending.

    from

    sys.columns sc

    inner join

    sys.indexes i

    on i.object_id = sc.object_id

    inner join

    sys.index_columns ic

    on ic.index_id = i.index_id

    and ic.object_id = sc.object_id

    and ic.column_id = sc.column_id

    where

    object_name (sc.object_id) = @TableName

    and

    (

    @TableSchema is null

    or object_schema_name (sc.object_id) = @TableSchema

    )

    group by

    OBJECT_SCHEMA_NAME (sc.object_id), -- TABLE_SCHEMA

    OBJECT_NAME (sc.object_id), -- TABLE_NAME

    sc.name -- COLIMN_NAME

    ),

    tableInfo

    as

    (

    -- Gather the info we want to display.

    select distinct

    c.TABLE_SCHEMA as [Schema],

    -- Show Primary Key

    case when pk.TABLE_NAME IS NOT NULL

    then

    'pk'

    else

    @Empty

    end as PK,

    -- Show FOREIGN KEY

    case when fk.TABLE_NAME IS NOT NULL

    then

    'fk'

    else

    @Empty

    end

    + -- Show when the foreign key is disabled.

    case

    when ISNULL (fk.FK_Disabled, 0) = 1 then

    ' (disabled)'

    else

    @Empty

    end

    as FK,

    -- Show index tableInfo, such as clustered / nonclustered, if unique, if descending, if disabled, etc.

    case

    when ix.COLIMN_NAME is not null then

    case

    when ix.type_desc = 'CLUSTERED' then 'c'

    when ix.type_desc = 'NONCLUSTERED' then 'nc'

    else ix.type_desc

    end

    + case

    when ix.is_unique = 1 then ', unique'

    else @Empty

    end

    + case

    when ix.is_descending_key = 1 then ', desc'

    else @Empty

    end

    + case

    when ix.is_disabled = 1 then ' (disabled)'

    else @Empty

    end

    else

    @Empty

    end

    as IX,

    -- Show the first of any other CONSTRAINTS

    ISNULL (oc.CONSTRAINT_TYPE, @Empty) as Cons,

    c.COLUMN_NAME as ColumnName,

    -- Show the data type.

    case

    when c.DATA_TYPE like '%int' then

    c.DATA_TYPE

    when c.DATA_TYPE = 'bit' then

    c.DATA_TYPE

    when c.DATA_TYPE like '%datetime' then

    c.DATA_TYPE -- + ' (' + CAST (c.DATETIME_PRECISION as VARCHAR) + ')'

    when c.DATA_TYPE like '%char%'then

    c.DATA_TYPE + ' (' + CAST (c.CHARACTER_MAXIMUM_LENGTH as VARCHAR) + ')'

    when c.NUMERIC_PRECISION IS NOT NULL and c.NUMERIC_SCALE IS NOT NULL then

    c.DATA_TYPE + ' (' + CAST (c.NUMERIC_PRECISION as VARCHAR) + ',' + CAST (c.NUMERIC_SCALE as VARCHAR) + ')'

    else

    c.DATA_TYPE

    end

    + -- After datatype, also show if identity.

    case

    when syscol.[status] = 128 then -- 128 = Identity

    ' (identity)'

    else

    @Empty

    end

    as DataType,

    case

    when c.IS_NULLABLE = 'yes' then

    @Y

    else

    @Empty

    end as Nullable,

    case

    when COLUMNPROPERTY(syscol.id, syscol.name, 'IsComputed') = 1 then

    @Y

    else

    @Empty

    end as Computed,

    -- Default value of column.

    c.COLUMN_DEFAULT as [Default],

    -- Show foreign key table if there is one

    case

    when fk.TABLE_NAME IS NOT NULL then

    fk.FK_Table

    else

    @Empty

    end as [FK Table],

    -- Show the column in the foreign key table to which the key relates.

    case

    when fk.TABLE_NAME IS NOT NULL then

    fk.FK_Field

    else

    @Empty

    end as [FK Field],

    -- Show the name of the constraint if there is one.

    case

    when fk.CONSTRAINT_NAME IS NOT NULL then

    fk.CONSTRAINT_NAME

    else

    @Empty

    end as [Constraint Name],

    -- For showing the results in the correct order.

    C.ORDINAL_POSITION

    from

    Information_Schema.Tables t

    inner join

    INFORMATION_SCHEMA.Columns c

    on c.TABLE_SCHEMA = t.TABLE_SCHEMA

    and c.TABLE_NAME = t.TABLE_NAME

    inner join

    syscolumns syscol

    on OBJECT_NAME(syscol.id) = t.TABLE_NAME

    and syscol.Name = c.COLUMN_NAME

    -- Including schema in join incurs big performance hit, and shouldn't be necessary (mostly).

    -- and OBJECT_SCHEMA_NAME (syscol.id) = c.TABLE_SCHEMA

    left outer join

    tableConstraints pk

    on pk.TABLE_SCHEMA = t.TABLE_SCHEMA

    and pk.TABLE_NAME = t.TABLE_NAME

    and pk.COLUMN_NAME = c.COLUMN_NAME

    and pk.CONSTRAINT_TYPE = 'PRIMARY KEY'

    left outer join

    tableConstraints fk

    on fk.TABLE_SCHEMA = t.TABLE_SCHEMA

    and fk.TABLE_NAME = t.TABLE_NAME

    and fk.COLUMN_NAME = c.COLUMN_NAME

    and fk.CONSTRAINT_TYPE = 'FOREIGN KEY'

    left outer join

    otherConstraint oc

    on oc.TABLE_SCHEMA = t.TABLE_SCHEMA

    and oc.TABLE_NAME = t.TABLE_NAME

    and oc.COLUMN_NAME = c.COLUMN_NAME

    left outer join

    tableIndexes ix

    on ix.TABLE_SCHEMA = t.TABLE_SCHEMA

    and ix.TABLE_NAME = t.TABLE_NAME

    and ix.COLIMN_NAME = c.COLUMN_NAME

    where

    t.TABLE_NAME = @TableName

    and

    (

    @TableSchema IS NULL

    or t.TABLE_SCHEMA = @TableSchema

    )

    )

    -- Return the results

    select

    [Schema],

    PK,

    FK,

    IX,

    Cons,

    ColumnName,

    DataType,

    Nullable,

    Computed,

    [Default],

    [FK Table],

    [FK Field],

    [Constraint Name]

    from tableInfo

    where

    (

    @TableSchema IS NULL

    or [Schema] = @TableSchema

    )

    and

    (

    @FieldNameFilter IS NULL

    or ColumnName LIKE @FieldNameFilter

    )

    order by

    [Schema],

    ORDINAL_POSITION

    -- Also return the list of triggers on this table if there are any, and if extended tableInfo is on.

    if @Extended = 1

    and exists (select * from sys.triggers where OBJECT_NAME (parent_id) = @TableName)

    begin

    select

    t.name as [Trigger Name],

    case when t.is_ms_shipped = 1 then @Y else @Empty end as [MS Shipped],

    case when t.is_disabled = 1 then @Y else @Empty end as [Disabled],

    case when t.is_instead_of_trigger = 1 then @Y else @Empty end as [Instead Of],

    case when t.is_not_for_replication = 1 then @Y else @Empty end as [Not for Replication],

    t.modify_date as Modified,

    t.create_date as Created

    from

    sys.triggers t

    where

    OBJECT_NAME (t.parent_id) = @TableName

    end

  • Highlighting the table in SMS and ALT+F1 gives pretty much the same data.

  • Hi nathan.j.lalonde, see my previous post, 3 up from yours.

  • The 2005 version posted worked great! Thanks to all of you.

  • We currently have data dictionaries for much of our data warehouse, but as we build new tables (and corresponding data dictionaries) this will be helpful. Thanks for the procedure!

    My minor modifications for 2005 were to set values (instead of default):

    declare @DBName varchar (20)

    Set @DBName = PARSENAME (@TableName, 3)

    declare @TableSchema varchar (20)

    Set @TableSchema = PARSENAME (@TableName, 2)

    set @TableName = PARSENAME (@TableName, 1)

    if @DBName is not null

    and @DBName != DB_NAME()

    begin

    print 'Cannot run this on DB ''' + @DBName + '''. Must be run on current DB.'

    return;

    end

    -- Set up some values for displaying the results.

    declare @Y varchar (10)

    Set @Y= ' y'; -- Spacing is for nicer look.

    declare @Empty varchar (1)

    Set @Empty = '';

    I've sent it to the rest of my team and will let you know what they think of it 🙂

  • Are going rework it for SQL Server 2005? Thanks.

  • Thanx, epriddy, glad to hear it.

    Bridget, that's been dome multiple times before. You can found SQL 2005 versions in other posts.

    Seems like reading previous posts before posting is not always a common habit. 😉

    Don't know if I can modify my article to include the 2K5 version there. I'll find out.

Viewing 15 posts - 16 through 30 (of 38 total)

You must be logged in to reply to this topic. Login to reply