February 3, 2005 at 9:35 am
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
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
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
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
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