Pass Database name in SP

  • 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

  • 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/

  • Would it be better to use ?

    IF @ObjectDB='myDB'

    BEGIN

    USE MyDB

    END

  • 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/

  • 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