SQL Server Performance

Prompt for DB Name

Discussion in 'T-SQL Performance Tuning for Developers' started by vaddi, Apr 30, 2007.

  1. vaddi New Member

    Hello,

    I have got a query , which gives the constraints in a database.

    select A.TABLE_SCHEMA, A.TABLE_NAME,A.COLUMN_NAME, A.CONSTRAINT_NAME, B.CONSTRAINT_TYPE
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as B
    join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as A
    on A.CONSTRAINT_SCHEMA = B.CONSTRAINT_SCHEMA
    and A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    and A.TABLE_SCHEMA = B.TABLE_SCHEMA
    and A.TABLE_NAME = B.TABLE_NAME
    where B.CONSTRAINT_TYPE in ( 'PRIMARY KEY', 'UNIQUE','FOREIGN KEY' )
    order by A.TABLE_SCHEMA, A.TABLE_NAME, B.CONSTRAINT_TYPE, A.CONSTRAINT_NAME;


    How can I make this query , so that it prompts the user to enter the database name for the query to run.

    Thanks
  2. MohammedU New Member

    You can create a procedure with sp_ prefix in master db and ask the user to execute in any db....
    or you can use dynamic sql..

    http://www.sommarskog.se/dynamic_sql.html


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page