information_schema.columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

information_schema.columns

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2013-11-11 : 12:52:03

Im trying to run something like this in the System db of SQL Server . It should loop through each db of the server (in my case, each company has their own db name).

If I use qa01.information_schema.columns instead of @CompanyName + .information_schema.columns , then it works fine (knowing qa01 is one of my dbs. The code below returns the following error.

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '+'.

DECLARE @CompanyName varchar(50)
DECLARE @UseSQL varchar (50)

DECLARE CompaniesCursor CURSOR FOR SELECT CompanyName FROM Company
OPEN CompaniesCursor

FETCH NEXT FROM CompaniesCursor
INTO @CompanyName

WHILE @@Fetch_Status = 0
BEGIN

IF EXISTS (SELECT column_name,*
FROM @CompanyName + .information_schema.columns --<<--error line
WHERE table_name = 'Import_Work_dpvHstTables'
AND column_name = 'DateTimeStamp')

print ' exists ' + @CompanyName
Else
print 'no exits ' + @CompanyNam

FETCH NEXT FROM CompaniesCursor
INTO @CompanyName

END

CLOSE CompaniesCursor
DEALLOCATE CompaniesCursor

Any ideas??
Much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-11 : 12:59:02

you cant pass dbname like this
for that you need dynamic sql.
if your attempt is to just loop through dbs then you can use system procedure sp_Msforeachdb


EXEC sp_Msforeachdb 'IF EXISTS (SELECT column_name,* 
FROM ?.information_schema.columns 
WHERE table_name = ''Import_Work_dpvHstTables'' 
AND column_name = ''DateTimeStamp'') 
print '' exists ?''
else
print ''not exists ?'''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-11-11 : 13:00:35

You cannot use a variable as a table name. You may need to use dynamic code.

djj

JAdauto
Posting Yak Master

160 Posts

Posted - 2013-11-11 : 16:09:04

Thanks Visakh16! This concept worked like a charm!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 01:49:40

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources