3d6TsGFj

1Create Procedure sp_is_referenced
2	@PrimaryTable nvarchar(100),
3	@DataId nvarchar(1000),
4	@Schema nvarchar(50) = 'dbo'
5As
6Begin
7
8Create Table #Data
9(
10	Id int identity(1, 1) not null,
11	PKTABLE_QUALIFIER nvarchar(100),
12    PKTABLE_OWNER       nvarchar(100),
13    PKTABLE_NAME        nvarchar(100),
14    PKCOLUMN_NAME       nvarchar(100),
15    FKTABLE_QUALIFIER   nvarchar(100),
16    FKTABLE_OWNER       nvarchar(100),
17    FKTABLE_NAME        nvarchar(100),
18    FKCOLUMN_NAME       nvarchar(100),
19    KEY_SEQ             nvarchar(100),
20    UPDATE_RULE         nvarchar(100),
21    DELETE_RULE         nvarchar(100),
22    FK_NAME             nvarchar(100),
23    PK_NAME             nvarchar(100),
24    DEFERRABILITY       nvarchar(100)
25)
26
27Insert into #Data
28	EXEC sp_fkeys @PrimaryTable, @Schema
29
30Declare @i int, @isExists bit, @sql nvarchar(max), @foreignColumn nvarchar(100), @foreignTable nvarchar(100)
31Set @i = 0
32
33While Exists (Select Top 1 Id from #Data where Id > @i)
34Begin
35	Select @i = Min(Id) from #Data where Id > @i
36	Select @foreignColumn = FKTABLE_OWNER + '.' + FKTABLE_NAME + '.' + FKCOLUMN_NAME,
37			@foreignTable = FKTABLE_OWNER + '.' + FKTABLE_NAME
38		from #Data where Id = @i
39
40	Set @sql = N'If Exists (Select Top 1 ' + @foreignColumn + ' from ' + @foreignTable + ' where ' + @foreignColumn + ' = ' + @DataId + ') Set @isExists = 1 Else Set @isExists = 0';
41	exec sp_executesql @sql, N'@isExists bit output', @isExists output
42
43	if (@isExists = 1)
44	begin
45		--end loop
46		Select @i = Max(Id) from #Data
47	end
48End
49
50Select @isExists as IsExists
51Drop Table #Data
52End
53
54--First Parameter is the Table Name which contais PK ID which needs to be checked
55-- Second Parameter is the PK Id of the table you selected
56--Third parameter is Schema name, provide empty when schema name is 'dbo'.
57-- Execute sp_is_referenced 'ItemGroup', 6, 'IMS'