Super Quick Table Meta Data

  • Thanks for the article. Nice little snippet to add to my bag of tricks.

    I usually create an ERD before creating the tables and keep that updated as things change.

    But this has uses on its own.

    Thanks go out to GSquared for doing the 2005 version.

    AJ Mendo | @SQLAJ

  • In addition, if there is more than one index on a column, it will show the number of indexes in brackets at the start. For example, the IX column might read '(2) c, unique, desc (disable)'

    when ix.COLIMN_NAME is not null then


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

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

    else ix.type_desc





    This functionality seems not to be working as I have 3 indexes on the same column but still see only β€œnc, desc” in IX column.

    Even the code extract shows that column IX always starts with the index type not the count.

    Am I having an old version of the code or is this functionality to be added in future?



  • Hi Arko Oige.

    No that is incorrect. I have no idea how that version of the code got there but it doesn't include the index count. It should look like the below.

    -- If there is more than 1 index on a column, indicate the number of indexes at start.


    when ix.IndexCount > 1 then

    '(' + CAST (ix.IndexCount as varchar) + ') '




    + case

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

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

    else ix.type_desc


    Also note that GSquared's 2005 version has another problem with showing up indexes correctly, but I haven't taken a look at that.

    Below is reattached the SQL 2008 version including the index count.

    Note, index count will only show up if there is more than 1 index.

    Let me know if it works for you.

  • Nice article. I have been working on a table based code generator and this may actually allow me to speed things up a little.

    Code does need tweaking for 2005. The setup section should read something like below to pass the execution.

    declare @DBName varchar (20)

    declare @TableSchema varchar (20)

    set @DBName = PARSENAME (@TableName, 3)

    set @TableSchema = PARSENAME (@TableName, 2)

    set @TableName = PARSENAME (@TableName, 1)

    if @DBName is not null

    and @DBName != DB_NAME()


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



    -- 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 = '';

  • Nice script! Thanks for sharing, Jacques!

    I made some mostly syntactical changes:

    - can't assign default values to local variables in SQL 2005

    - character case differences will break the code in BINARY (or case sensitive -?) collations

  • Just found this article and am thrilled because I am working on a new contract with a crazy DB system. This is immensely valuable except I need it for SQL2K. Has anyone tackled this yet? I am currently trying to re-work it for SQL2K but my knowledge of system tables is limited so it's taking me a while to inch along. If I get it to work I will share unless someone posts a SQL2K version first.

    Thanks again Jacques!

  • OK took a stab at making it SQL2K compatible and added Meta Data Description as an extra column. Not perfect but so far it seems like it works on most of my tables.

  • Hi Santiago.

    Very glad it is of use to you. I still use it almost every day myself. πŸ™‚

    Thanx for the 2K work. I hope I never need it! πŸ™‚

Viewing 8 posts - 31 through 38 (of 38 total)

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