September 17, 2010 at 4:38 am
Hi,
For Eg.
Table Name: Location
Fileds: LocationID,LocationName,..etc Primary Key is LocationID.
In this, if user try to delete the location master records, here we need to find referrrence value in all table.
In case referrence table(LocatoinID) contains arount 40 Tables. here i need to find each and every table, whether LocationId exists or not.
Is there any short cut procedures or funtions to return referenced table (i.e LocationID value refferd in particular table).
Some tables contains:Single PrimaryKey or Composite Primary Key
Can any please guide me through sample code
September 17, 2010 at 4:53 am
if your query about finding a specific column in related tables then
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%your column name%'
ORDER BY schema_name, table_name;
----------
Ashish
September 17, 2010 at 5:00 am
Thanks for your reply. I have taken reference table and column already. But my requirement is, need to find whether particular Value exists in any reference table. How can i do , through any common funtion or procedure.
September 17, 2010 at 5:07 am
September 17, 2010 at 5:22 am
For Eg.
LocationID-23
This LocationID value may refer in many table. here i need to find, whether any table referred this value.
If i send TableName and LocationID VAlue(23).That should search combination of key(primary or composite key in all reference table and return the result)
How can i achieve this.
September 17, 2010 at 7:33 am
The system view INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS will tell you which unique constraint is referenced by each foreign key constraint. The system view INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE will tell you which columns are used in each of the constraints. Joining these two system views on the unique constraint name will give you foreign keys that reference each column. You can filter those results based on the particular column that you are interested in.
If you want to make this more efficient, you can look at the definitions of each of the systems views to only pull the information that you need to get your results.
SELECT *
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as ccu
ON rc.Unique_Constraint_Name = ccu.Constraint_Name
WHERE ccu.Column_Name = 'YourColumnName'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 21, 2010 at 4:02 am
AFIK, there is no standard functionality for that. You'll have to write that yourself. What I did (for my employer, so I can't disclose the full code) was to generate dynamic SQL based on the references found. Here's the code that finds all references into the table being researched. These references may be directly (i.e. the referencing table references the researched table) or they may be indirectly (a table references a table that references the researched table and that may be repeated several levels down). Furthermore this code will deal correctly with tables with a single pk column and more complicated tables with multi column keys.
declare @object_id int;
declare @nRows int;
select @object_id = object_id('dbo.Orders');
-- Memory table to hold all columns referencing the primary key columns
-- of the table we are researching.
declare @tblReferenceColumns table (
constraint_object_id int not null,
referenced_object_id int not null,
referenced_column_id int not null,
parent_object_id int not null,
parent_column_id int not null,
primary key(constraint_object_id, parent_object_id, parent_column_id)
);
-- First find all columns in the referencing tables that directly reference our table's primary key columns.
insert @tblReferenceColumns(constraint_object_id, referenced_object_id, referenced_column_id, parent_object_id, parent_column_id)
select fkc.constraint_object_id, fkc.referenced_object_id, fkc.referenced_column_id, fkc.parent_object_id, fkc.parent_column_id
from sys.key_constraints kc
inner join sys.index_columns ic on (ic.object_id = kc.parent_object_id and ic.index_id = kc.unique_index_id)
inner join sys.foreign_key_columns fkc on (fkc.referenced_object_id = ic.object_id and fkc.referenced_column_id = ic.column_id)
where kc.type = 'PK'
and kc.parent_object_id = @object_id;
select @nRows = @@rowcount;
-- Repeat until no new rows were added.
while @nRows > 0
begin
-- Find all foreign keys that reference all of the columns referenced by
-- one of the references already in our list.
insert @tblReferenceColumns(constraint_object_id, referenced_object_id, referenced_column_id, parent_object_id, parent_column_id)
select distinct fkc.constraint_object_id, rcol.referenced_object_id, rcol.referenced_column_id, fkc.parent_object_id, fkc.parent_column_id
from @tblReferenceColumns rcol
inner join sys.foreign_key_columns fkc on (fkc.referenced_object_id = rcol.parent_object_id and fkc.referenced_column_id = rcol.parent_column_id)
where not exists (
select top 1 *
from @tblReferenceColumns r
left outer join sys.foreign_key_columns f on (f.referenced_object_id = r.parent_object_id and f.referenced_column_id = r.parent_column_id)
where r.constraint_object_id = rcol.constraint_object_id
and r.parent_object_id = rcol.parent_object_id
and f.referenced_object_id is null-- not exists
)
and not exists (
select top 1 *
from @tblReferenceColumns r
where r.constraint_object_id = fkc.constraint_object_id
and r.parent_object_id = fkc.parent_object_id
and r.parent_column_id = fkc.parent_column_id
);
select @nRows = @@rowcount;
end
-- If one foreign key in our list is fully covered by another foreign key on the same columns, the
-- same set of data will be returned. We can remove either one of these foreign keys from our list
-- to reduce the number of searches we have to perform.
while exists (
select top 1 *
from @tblReferenceColumns r1
inner join @tblReferenceColumns r2 on (r2.constraint_object_id <> r1.constraint_object_id and r2.referenced_object_id = r1.referenced_object_id and r2.referenced_column_id = r1.referenced_column_id and r2.parent_object_id = r1.parent_object_id and r2.parent_column_id = r1.parent_column_id)
where not exists (
select top 1 *
from @tblReferenceColumns c1
left outer join @tblReferenceColumns c2 on (c2.constraint_object_id <> c1.constraint_object_id and c2.referenced_object_id = c1.referenced_object_id and c2.referenced_column_id = c1.referenced_column_id and c2.parent_object_id = c1.parent_object_id and c2.parent_column_id = c1.parent_column_id)
where c1.constraint_object_id = r1.constraint_object_id
and c1.referenced_object_id = r1.referenced_object_id
and c1.parent_object_id = r1.parent_object_id
and c2.referenced_object_id is null-- not exists
)
)
delete rcol
from @tblReferenceColumns rcol
where rcol.constraint_object_id in (
select top 1 r1.constraint_object_id
from @tblReferenceColumns r1
inner join @tblReferenceColumns r2 on (r2.constraint_object_id <> r1.constraint_object_id and r2.referenced_object_id = r1.referenced_object_id and r2.referenced_column_id = r1.referenced_column_id and r2.parent_object_id = r1.parent_object_id and r2.parent_column_id = r1.parent_column_id)
where not exists (
select top 1 *
from @tblReferenceColumns c1
left outer join @tblReferenceColumns c2 on (c2.constraint_object_id <> c1.constraint_object_id and c2.referenced_object_id = c1.referenced_object_id and c2.referenced_column_id = c1.referenced_column_id and c2.parent_object_id = c1.parent_object_id and c2.parent_column_id = c1.parent_column_id)
where c1.constraint_object_id = r1.constraint_object_id
and c1.referenced_object_id = r1.referenced_object_id
and c1.parent_object_id = r1.parent_object_id
and c2.referenced_object_id is null-- not exists
)
)
select * from @tblReferenceColumns;
Hope to have helped you a bit with this.
September 21, 2010 at 4:08 am
Oh and by the way: if your concern is just to delete all referencing rows when the end user deletes a referenced row, then you may want to have a look at the 'on delete cascade' clause on the foreign key definition: http://msdn.microsoft.com/en-us/library/ms174979.aspx
This way MSSQL deletes all referencing rows automatically for you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply