To get all referrenced columns - Need TSQL script

If I pass table name and column name it should return all the Table names and columns where the given column is referred as FK.  How to achieve this? Please do assist.

Ex: GetAllReference('Employee','EmpID')

Should retun as given below

TABLE            COLUMN
-----------------------------------------
Dependent    DependentEmpID
PolicyAsist     EmpID.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
The query below is based on a query designed to return all FK relationships in a given DB.  The source was originally from Pinal Dave (http://blog.SQLAuthority.com)


SELECT
	FK_Table = FK.TABLE_NAME,
	FK_Column = CU.COLUMN_NAME,
--	PK_Table = PK.TABLE_NAME,
--	PK_Column = PT.COLUMN_NAME,
	FK_Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
	SELECT i1.TABLE_NAME, i2.COLUMN_NAME
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
	WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
	) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
WHERE PK.TABLE_NAME = 'Employee'
AND PT.COLUMN_NAME = 'EmpID'

Open in new window

0
lcohanDatabase AnalystCommented:
Try this:


create procedure GetAllReference(@tabname nvarchar(50),@colname nvarchar(50))
as
set nocount on;

IF @tabname=null AND @colname=null
RETURN;
ELSE
BEGIN

declare @sqlstr nvarchar(4000);
set @sqlstr =
      'select o.name as table_name,c.name as column_name
            from sys.syscolumns c inner join sys.all_objects o on o.object_id = c.id'

--if both table and column name are passed
IF @tabname is not null AND @colname is not null
      set @sqlstr = @sqlstr + ' and o.name like '+'''%'+@tabname+'%''' +' and c.name like '+'''%'+@colname+'%'''

IF @tabname is null AND @colname is not null --if ONLY column name is passed then return ALL tables in the DB having column LIKE that
      set @sqlstr = @sqlstr + ' and c.name like '+'''%'+@colname+'%'''

IF @tabname is not null AND @colname is null --if ONLY table name is passed then return ALL tables in the DB having name LIKE that
      set @sqlstr = @sqlstr + ' and o.name like '+'''%'+@tabname+'%'''

--print @sqlstr;
exec sp_executesql @sqlstr;

END
GO


/*
--usage:
exec GetAllReference @tabname=null,@colname=null
exec GetAllReference @tabname='client',@colname='id'
exec GetAllReference @tabname=null,@colname='ID'
exec GetAllReference @tabname='ID',@colname=null
*/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Easwaran ParamasivamAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.