Hi ,It has been almost 2 months since i blogged;was stuck up with my project task,but i learnt many things thought of sharing with you.May be couple of days I may post on SQL Server probably queries which I got to know from Database Administrator,hope u will enjoy reading
To Find all Foreign Keys pointing to a Table
Listing all foreign keys in a Database
select distinct object_name(o.parent_obj) as ChildTable from dbo.sysforeignkeys
Listing all the available tables stored procedure,views
select * from sysobjects where xtype=‘u/p/v/f/fn’
Listing all foreign keys in a tABLE
————————————
select o.name as ForeignKeyName, object_name(o.parent_obj) as ChildTable,
c.name as ReferencingColumnName,object_name(rkeyid) as ParentTable, pc.name as ReferencedColumnName
from dbo.sysforeignkeys
join sysobjects o on constid = o.id
join syscolumns c on c.id = o.parent_obj and c.colid = fkey
join syscolumns pc on pc.id = rkeyid and pc.colid = rkey
where object_name(o.parent_obj)=‘Contact’ order by ChildTable
Listing all primary keys in a table
SELECT Col.Column_Name from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = ‘PRIMARY KEY ‘
AND Col.Table_Name = ‘<your table name>’
Listing all primary keys in a database
select * from information_schema.table_constraints where constraint_type = ‘Primary Key’
select distinct object_name(o.parent_obj) as ChildTable
from dbo.sysforeignkeys
join sysobjects o on constid = o.id
join syscolumns c on c.id = o.parent_obj and c.colid = fkey
join syscolumns pc on pc.id = rkeyid and pc.colid = rkey
order by ChildTable
