SQL Server Performance Forum – Threads Archive

Prompt for DB Name

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

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.

]]>