Skip to content

Instantly share code, notes, and snippets.

@jujiro
Created May 20, 2019 16:37
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save jujiro/15f8e52b40294281f560d114870284c1 to your computer and use it in GitHub Desktop.
Find table dependencies (Sql Server)
begin
-- Use this script to find the dependencies of tables.
-- Set the following two variables.
-- ####################################################
declare @TableSchema varchar(255)='Production'
declare @TableName varchar(255)='Product'
-- ####################################################
declare @nCnt1 int
declare @nCnt2 int
declare @Crsr cursor
declare @PKTable varchar(255)
declare @Msg varchar(255)
declare @Tab table (
[Owner] varchar(255),
PKTable varchar(255),
PKColumn varchar(255),
FKOwner varchar(255),
FKTable varchar(255),
FKColumn varchar(255),
Id int identity,
Priority int)
set nocount on
select
@nCnt1=1
from
INFORMATION_SCHEMA.TABLES
where
table_name=@TableName and
table_schema=@TableSchema
if @@rowcount=0
begin
set @Msg='No table found '+@TableName
raiserror (@Msg,16,1) with nowait
end
delete @Tab
insert into @Tab (
[Owner],
PKTable,
PKColumn)
SELECT
@TableSchema,
@TableName,
column_name
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON
TC.CONSTRAINT_TYPE = 'PRIMARY KEY' and
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME and
ku.table_name=@TableName and
tc.table_schema=@TableSchema and
ku.table_schema=@TableSchema
ORDER BY
KU.TABLE_NAME,
KU.ORDINAL_POSITION
while 1=1
begin
select
@nCnt1=Count(*)
from
@Tab
insert into @Tab (
[Owner],
PKTable,
PKColumn,
FKOwner,
FKTable,
FKColumn)
select
pk.table_schema as pkowner,
pk.table_name as pktable,
pk.column_name as pkcolumn,
fk.table_schema as fkowner,
fk.table_name as fktable,
fk.column_name as fkcolumn
from
information_schema.REFERENTIAL_CONSTRAINTS c,
information_schema.KEY_COLUMN_USAGE fk,
information_schema.KEY_COLUMN_USAGE pk
where
c.constraint_schema=fk.constraint_schema and
c.constraint_name=fk.constraint_name and
c.unique_constraint_schema=pk.constraint_schema and
c.unique_constraint_name=pk.constraint_name and
pk.ordinal_position=fk.ordinal_position and
pk.table_name in (
select
PKTable
from
@Tab
union
select
FKTable
from
@Tab) and
not exists (
select
1
from
@Tab t
where
t.[Owner]=pk.table_schema and
t.PKTable=pk.table_name and
t.PKColumn=pk.column_name and
t.FKOwner=fk.table_schema and
t.FKTable=fk.table_name and
t.FKColumn=fk.column_name) and
1=1
order by
pk.table_schema,
pk.table_name,
pk.ordinal_position
select
@nCnt2=Count(*)
from
@Tab
if @nCnt1=@nCnt2
break
end
update @Tab set
[Priority]=[Id]
update @Tab set
[Priority]=mx.[Id]
from
@Tab t join
(
select
x.FKTable,
Max(x.Id) as [Id]
from
@Tab x
group by
x.FKTable) mx
on
mx.FKTable=t.FKTable
select
PKTable,
PKColumn,
FKTable,
FKColumn,
[Priority]
from
@Tab
order by
[Priority],
[Id]
end
go
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment