February 15, 2011 at 4:08 am
I have created a stored procedure that takes 2 parameters which are 2 database names. Within the procedure I loop through the sysobjects and get a list of tables. The code I am using is as follows
DECLARE table1CRS1 Cursor FOR
SELECT * FROM [@DBNAME2].dbo.sysobjects WHERE xtype = 'U'
OPEN table1CRS1
I keep getting an error:
invalid object name '@DBNAME2.dbo.sysobetcs'
I am not sure how I shoud code the variable. Any ideas
February 15, 2011 at 5:59 am
Hi,
The error occurs because you're trying to use object names (tables, columns, etc.) in variables. In order to achieve what you want you need to use dynamic SQL.
See the example below:
DECLARE @DBName SYSNAME
SET @DBName = 'MYDBNAME'
DECLARE @strSQL VARCHAR(4000)
SET @strSQL = 'SELECT * FROM ' + @DBName + '.dbo.sysobjects WHERE xtype = ' + CHAR(39) + 'U' + CHAR(39)
EXECUTE (@strSQL)
Andreas Goldman
February 15, 2011 at 6:05 am
Hi,
Thanks for that, will make amendments now. Much appreciated.
February 16, 2011 at 2:39 pm
No problems.
I don't know exactly what your planned usage is, however if you'd like to be protected against SQL injection you should consider using sp_executesql instead of EXECUTE.
Andreas Goldman
February 17, 2011 at 7:00 pm
Hi
You could also use:
execute sp_MSforeachDB @command1="use [?] select * from dbo.sysobjects where xtype='U'"
Keep in mind that system databases will be listed as well, although I don't think that should be an issue.
🙂
February 18, 2011 at 3:20 am
hey learned a lot from this Thanks...!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply