May 25, 2012 at 9:10 am
I am creating sp where I need to pass tablename and database name in order to query INFORMATION_SCHEMADB
How do I attach @ObjectDB (DB Name) to my statement below?
@TableName VARCHAR(50),
@ObjectDB VARCHAR(20)
AS
SELECT c.COLUMN_NAME, c.DATA_TYPE
,IsPKey = ISNULL(tblPrimaryKey.IsPKey,0)
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (SELECT ccu.TABLE_CATALOG,ccu.TABLE_NAME,ccu.COLUMN_NAME
,IsPKey = 1
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
,INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE ccu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
AND ccu.TABLE_NAME = tc.TABLE_NAME
AND tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND ccu.TABLE_NAME = @TableName
) tblPrimaryKey
ON c.TABLE_CATALOG = tblPrimaryKey.TABLE_CATALOG
AND c.TABLE_NAME = tblPrimaryKey.TABLE_NAME
AND c.COLUMN_NAME = tblPrimaryKey.COLUMN_NAME
WHERE c.TABLE_NAME = @TableName
May 25, 2012 at 9:26 am
You will to convert the entire thing to dynamic sql.
You can reference objects from any database on the server with full 3 part naming convention (database.schema.object)
Using a variable like this you will have to convert it all to dynamic sql. Something like this should work.
declare @sql varchar(max)
set @sql =
'SELECT c.COLUMN_NAME
,c.DATA_TYPE
,IsPKey = ISNULL(tblPrimaryKey.IsPKey, 0)
FROM ' + @ObjectDB + '.INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN
(
SELECT ccu.TABLE_CATALOG
,ccu.TABLE_NAME
,ccu.COLUMN_NAME
,IsPKey = 1
FROM ' + @ObjectDB + '.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
join ' + @ObjectDB + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on ccu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
AND ccu.TABLE_NAME = tc.TABLE_NAME
AND tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = ''PRIMARY KEY''
AND ccu.TABLE_NAME = ''' + @TableName + '''
) tblPrimaryKey ON c.TABLE_CATALOG = tblPrimaryKey.TABLE_CATALOG
AND c.TABLE_NAME = tblPrimaryKey.TABLE_NAME
AND c.COLUMN_NAME = tblPrimaryKey.COLUMN_NAME
WHERE c.TABLE_NAME = ''' + @TableName + ''''
exec (@sql)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 9:31 am
Would it be better to use ?
IF @ObjectDB='myDB'
BEGIN
USE MyDB
END
May 25, 2012 at 9:41 am
borislava99 (5/25/2012)
Would it be better to use ?IF @ObjectDB='myDB'
BEGIN
USE MyDB
END
What would you do if @ObjectDB = 'YourDB'? If you do a bunch of hard coded conditionals this proc will out of date everytime you add/drop a database. You will still have to have some dynamic sql for this.
This should demonstrate something like that.
Create procedure DBTest
(
@ObjectDB varchar(25)
)
as begin
exec('use ' + @ObjectDB)
select * from sys.sysobjects where xtype = 'u'
end
You should of course query sys.sysdatabases at the beginning to make sure the database actually exists. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 10:32 am
borislava99 (5/25/2012)
Would it be better to use ?IF @ObjectDB='myDB'
BEGIN
USE MyDB
END
Msg 154, Level 15, State 1, Procedure TestProc, Line 4
a USE database statement is not allowed in a procedure, function or trigger.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply