Pass Database name in SP

Question

Post reply

Pass Database name in SP

borislava99

Grasshopper

Points: 10
More actions
May 25, 2012 at 9:10 am

#261495

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286602 More actions · Answer 1

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/

borislava99 Grasshopper Points: 10 More actions · Answer 2

Would it be better to use ?

IF @ObjectDB='myDB'

BEGIN

USE MyDB

END

Sean Lange SSC Guru Points: 286602 More actions · Answer 3

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/

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 4

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

Pass Database name in SP

Cookies on SQLServerCentral