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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy