July 30, 2004 at 9:00 am
Is it possible to prefix a database name to the syscolumns system table to access column information on another database?
e.g.
I'm accessing DB_1 however I need to alter a table in DB_2 by adding a new column if it does not exist. Ideally this syntax would work:
SELECT *
FROM DB_2..syscolumns
WHERE name = '<column_name>'
AND id = object_id('<table_name>')
I got this from using this query:
SELECT * FROM master..sysdatabases WHERE NAME = 'DB_2'
master is a prefix on sysdatabases but that system table is only available there, so maybe that's why the prefix works.
My solution is to use the INFORMATION_SCHEMA.COLUMNS view since the scripts we run have admin rights, so we can do this validation:
SELECT 'True'
FROM DB_2.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = <table_name>'
AND COLUMN_NAME = '<column_name>'
But I'd prefer to use syscolumns since that's what we normally use (standards)
Steve Kumbsky
Steve Kumbsky
July 30, 2004 at 3:44 pm
Yes. Your Select statement is ok. Just remember to qualify the tablename (otherwise SQL thinks the table should be in the current database).
SELECT *
FROM DB_2..syscolumns
WHERE name = '<column_name>'
AND id = object_id('<database_name>.<owner_name>.<table_name>')
Steve
July 30, 2004 at 4:31 pm
And if you want to be really sure, you can even go as far as naming the server such as
...
.
Note that if you are doing a link to a remote server you need to use the owner name and not the .. as in
"sqlserver1.master.dbo.sysdatabases" and not "sqlserver1.master..sysdatabases"
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
August 2, 2004 at 6:41 am
Thank you both for your replys...the qualifying of the table name on the ID = filter was the missing piece for me. Thanks again.
Steve
Steve Kumbsky
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply