Post reply

Automatic column table tracking

  • February 3, 2005 at 9:35 am

    #87579

    Hello,

    Task: Automatically register any new table creation along with the columns into an user defined table.

    Detail explanation: I am trying to capture the information in sysobjects and syscolumns and store them into a user defined table called dbo.ColumnTableMap. I need accomplish this automatically, what I mean by this is when a user creates a table, that should raise a trigger that will insert data into dbo.ColumnTableMap table.

    Part of the solution:

    SELECT so.name, sc.name

    FROM sysobjects so

    INNER JOIN syscolumns sc ON so.id=sc.id

    WHERE so.xtype = 'U'

    ORDER BY so.name

    This would give me the table name and column name for my existing tables.

    I am thinking about having INSERT TRIGGERS on syscolumns and sysobjects. I am wondering what could be the drawbacks in doing this.

    And another question, lets say there is description for each column, how & where can I find this information.

    Thank you for your help.

    Ram

  • February 3, 2005 at 9:41 am

    #540430

    Can't put triggers on system tables, so you'll need to maintain snapshot tables, and run queries against the snapshot to determine what changed in a given time period.

    As for descrition, if you're talking about the extended column description that you can enter throught the EM table designer, this is stored as an extended property.

    Read the BOL on topic FN_LISTEXTENDEDPROPERTY for info on how to retrieve this.

     

  • February 3, 2005 at 10:04 am

    #540444

    Just to complete PW's post, you could also look up the table SysProperties since you seem to like to do your own queries.

  • February 3, 2005 at 10:18 am

    #540451

    Hi Remi,

    What other ways can I accomplish this task, if I choose not to use my own query.

    Thank you.

  • February 3, 2005 at 11:21 am

    #540469

    As PW was stating (for extended properties) :

    SELECT *

    FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '', 'column', default)

    .

    Otherwise I would suggest you check out the INFORMATION_SCHEMA views in the master's table.

    Run this from any database that you wish to extract info from :

    Select * from INFORMATION_SCHEMA.COLUMNS

Viewing 5 posts - 1 through 4 (of 4 total)

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