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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-06 : 09:12:06
|
| Allan McLemore writes "I have a stored procedure that needs to check for the existence of a table in multiple databases. I want to be able to run the stored procedure from 1 centralized database, so I do not have to deploy this same stored procedure to all databases. I tried using dynamic SQL to perform this action, but I get an error.Here is the dynamic SQL code:-----------------------------Set @vcSQL = 'Select table_name from MyDatabase.information_schema.tables where table_schema = ''dbo'' and table_name = ''MyTable'' and table_type=''Base Table''' Exec @vcSQLHere is the error:-------------------Server: Msg 911, Level 16, State 1, Line 18Could not locate entry in sysdatabases for database 'Select table_name from MyDatabase'. No entry found with that name. Make sure that the name is entered correctly.When I run the same SQL statement contained in @vcSQL directly from the Query Analyzer without using the Exec command, the SQL statement executes just fine.SQL statement run from Query Analyzer-------------------------------------Select table_name from MyDatabase.information_schema.tables where table_schema = 'dbo' and table_name = 'MyTable' and table_type='Base Table' Why don't the 'information_schema' views seem to work from dynamic SQL? Is there a workaround? I definitely do not want to deploy my stored procedure to every single database that I am running checks for." |
|
|
dsdeming
479 Posts |
Posted - 2002-05-06 : 09:30:04
|
| Try changing the execute line to Exec ( @vcSQL ). |
 |
|
|
|
|
|
information_schema views not working in dynamic SQL