September 5, 2008 at 8:20 am
I gathered some code from David Jackson's site and made a Stored Procedure out of it.
This code returns the tables and foreign keys for a table when you pass in the table name and DB.
However, this requires you to run the SP on the DB you want to query. I'd like to have the
SP in master and be able to run it on any DB to return the data requested for the Table/DB passed.
How would I do that, is it possible?
CREATE PROCEDURE [dbo].[rr_Get_ForeignKeyTables] @TableName varchar(128) -- targettable
, @DB varchar(128) -- target database
AS
BEGIN
WITH T_CONTRAINTES (table_name, father_table_name, constraint_name)
AS (SELECT DISTINCT CTU.TABLE_NAME, TCT.TABLE_NAME, RFC.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RFC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU
ON RFC.CONSTRAINT_CATALOG = CTU.CONSTRAINT_CATALOG
AND RFC.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA
AND RFC.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
ON RFC.UNIQUE_CONSTRAINT_CATALOG = TCT.CONSTRAINT_CATALOG
AND RFC.UNIQUE_CONSTRAINT_SCHEMA = TCT.CONSTRAINT_SCHEMA
AND RFC.UNIQUE_CONSTRAINT_NAME = TCT.CONSTRAINT_NAME
WHERE CTU.TABLE_CATALOG = @DB)
,T_TREE_CONTRAINTES (table_to_delete, level, constraint_name)
AS (SELECT DISTINCT table_name, 0, constraint_name
FROM T_CONTRAINTES
WHERE father_table_name = @TableName
UNION ALL
SELECT priorT.table_name, level - 1, priorT.constraint_name
FROM T_CONTRAINTES priorT
INNER JOIN T_TREE_CONTRAINTES beginT
ON beginT.table_to_delete = priorT.father_table_name
WHERE priorT.father_table_name <> priorT.table_name)
SELECT DISTINCT *
FROM T_TREE_CONTRAINTES
ORDER BY level
END
September 5, 2008 at 9:18 am
You could just create it in master and when you run it use:
EXEC master.dbo.rr_Get_ForeignKeyTables @TableName = 'ttt', @DB = 'ddd'
Greg
September 5, 2008 at 10:08 am
Nah, still doesn't work because it seems to be running the select ON master.
September 5, 2008 at 10:19 am
You could just create it in master and when you run it use:
EXEC [sqlserver].master.dbo.rr_Get_ForeignKeyTables @TableName = 'ttt', @DB = 'ddd'
if you have set up a linked server
September 5, 2008 at 12:05 pm
I don't think you'd need a linked server if it's on the same instance. Try creating the proc in master as
sp_rr_Get_ForeignKeyTables.
Greg
September 5, 2008 at 12:34 pm
As usual, security has been optimized in sql2005 !
For a proc to work in a database context, it needs to be in that database,
except for procs (residing in master db) which are marked as systemobject.
If that proc is prefixed sp_ you don't need to qualify is with master.dbo.
exec sys.sp_MS_marksystemobject [sp_DBA_RowCount]
Make sure you can easy differentiate your own proc form the MS-proc,
so it is clear to everybody it is propriatary !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 5, 2008 at 1:14 pm
This is a case of scope. The INFORMATION_SCHEMA VIEWS and the 2005 system views (sys.objects, sys.tables, etc) require you to be in the database you are looking at.
This is one case where using dynamic sql is likely the best way to achieve the desired result.
I have several of these types of utility SP's that go directly against the system tables to gather the information for things like creating the declaration statements for all fields in a table, creating formatted select, insert, update, or delete statements including having the table alias, etc. This allows me to cut and paste into my SPs and seriously increases my productivity. All of these SPs start with SP_ 🙂
One of my more useful utilities is called SP_Find. It searches syscomments for the passed in string value and returns a list of SPs, Functions, and views and their object types. This allows me to know what I have to refactor when changing a field name or modify a table.
Gary Johnson
Sr Database Engineer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply