October 31, 2005 at 9:52 am
Hello everyone,
I'm new to these forums. I've been working with SQL server for a little over a year now. I've recently needed to query the information_schema for table column names. The problem is that I will need to query different databases and need to pass the database name as a parameter to the stored procedure. What I came up with was:
SELECT * FROM @strDBName.information_schema.COLUMNS
but, of course, this does not work. I've looked all around and cannot find the solution for this. Is there a function I need to use, or a work-around for this?
Any help would be greatly appreciated,
Thank you in advance,
TishaL
October 31, 2005 at 10:08 am
Are you familiar with "exec()"?
declare @db nvarchar(100)
set @db='pubs'
exec('SELECT * FROM '+@db+'.information_schema.COLUMNS')
October 31, 2005 at 10:32 am
Thank you, that part works now, but a part I had working, now doesn't.
SELECT TABLE_NAME, COLUMN_Name FROM database.information_schema.COLUMNS WHERE TABLE_NAME <> dtproperties ORDER BY TABLE_NAME
This worked when I put a dbName in place of "database". When I change to what you showed me,
ALTER PROCEDURE
tsp_subscr_GetFields
(
@strDB
nvarchar(100)
)
AS
Begin
Set NoCount On
declare @prop nvarchar(50)
set @prop = 'dtproperties'
exec('SELECT TABLE_NAME, COLUMN_NAME FROM ' + @strDB + '.information_schema.COLUMNS WHERE TABLE_NAME <> ' + @prop + ' ORDER BY TABLE_NAME')
It tells me "Invalid column name 'dtproperties'." in query analyzer.
Any suggestions? Please?
TishaL
October 31, 2005 at 11:44 am
declare @db_name nvarchar(30), @cmd nvarchar(200)
set @db_name = N'MY_DB'
set @cmd = N'SELECT TABLE_NAME, COLUMN_NAME FROM '+@DB_NAME+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME <> ''dtproperties'' order by table_name, column_name'
exec sp_executesql @cmd
October 31, 2005 at 12:42 pm
I still get "Invalid column name 'dtproperties'."
Here is my code exactly:
declare @cmd nvarchar(200)
set @cmd = N'SELECT TABLE_NAME, COLUMN_NAME FROM BW_P3.information_schema.COLUMNS
WHERE TABLE_NAME <> "dtproperties" ORDER BY TABLE_NAME'
exec sp_executesql @cmd
This line works just fine:
select TABLE_NAME, COLUMN_NAME FROM BW_P3.information_schema.COLUMNS WHERE TABLE_NAME <> 'dtproperties'
I don't understand what the difference is, or what I'm doing wrong. I really need to get this info into a dataset, without the dtproperties table or the system tables.
Please help me,
TishaL
November 1, 2005 at 2:15 am
Tishal, to mark a text inside text, you have to use two single quotes - not a double quote. In your case, just replace the " around dtproperties with '' and it will work.
declare @cmd nvarchar(200)
set @cmd = N'SELECT TABLE_NAME, COLUMN_NAME FROM BW_P3.information_schema.COLUMNS
WHERE TABLE_NAME <> ''dtproperties'' ORDER BY TABLE_NAME'
exec sp_executesql @cmd
November 2, 2005 at 2:16 pm
In the code that I had posted above, the quotes were two single quotes rather than the double quote :
declare @db_name nvarchar(30), @cmd nvarchar(200)
set @db_name = N'pubs'
set @cmd = N'SELECT TABLE_NAME, COLUMN_NAME FROM '+@DB_NAME+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME <> ''dtproperties'' order by table_name, column_name'
exec sp_executesql @cmd
This gives out output as you need. You can cut and paste the code from above as-is and try it out...then modify it as per your needs.
November 2, 2005 at 2:24 pm
thank you all very much. I ended up with:
declare @prop nvarchar(20), @con nvarchar(20), @seg nvarchar(20)
set @prop = '''dtproperties'''
set @con = '''sysconstraints'''
set @seg = '''syssegments'''
set @cmd = N'SELECT TABLE_NAME, COLUMN_NAME FROM ' + @strDB + '.information_schema.COLUMNS
print @cmd
exec sp_executesql @cmd
which gives the same results, i guess my mind just wasn't working the other day. Thank you again. I now know where to go for any SQL questions. You guys are great!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply