Adsense

Tuesday, July 8, 2014

View all dependencies within a SQL Server Instance

This query will show all dependencies in a SQL Server Instance.


if exists (select 1 from sys.objects where name = 'table_depend_staging')
      drop table table_depend_staging
go

create table dbo.table_depend_staging(
      referencing_database_name sysname,
      referencing_schema_name sysname,
      referencing_entity_name sysname,
      referencing_object_type varchar(2),
      referencing_object_type_desc varchar(100),
      referenced_database_name sysname,
      referenced_schema_name sysname,
      referenced_entity_name sysname,
      referenced_object_type varchar(2),
      referenced_object_type_desc varchar(100))
go

declare @dbname sysname
declare @sql nvarchar(2000)

declare curs cursor for
select name
from master.sys.databases

open curs

fetch next from curs into @dbname

/******************************************GET ALL DEPENDENCIES**************************************/

while @@FETCH_STATUS = 0
begin

set @sql =  'use ' + @dbname + '
      select
      ''' + @dbname + ''' as referencing_database_name,
      s.name as referencing_schema_name,
      o.name as referencing_entity_name,
      o.type as object_type,
      o.type_desc as object_type_desc,
      ISNULL(referenced_database_name, ''' + @dbname + ''') as referenced_database_name,
      case
            when len(referenced_schema_name) = 0 or referenced_schema_name is null then ''dbo''
            else referenced_schema_name
      end as referenced_schema_name, --often this is an emtpy string
      referenced_entity_name
      from sys.sql_expression_dependencies ed
      inner join ' + @dbname + '.sys.objects o on o.object_id = ed.referencing_id
      inner join ' + @dbname + '.sys.schemas s on s.schema_id = o.schema_id
      where referenced_class not in (12, 13) --not including 12 (ddl trigger) or 13 (system ddl trigger)'

      insert into dbo.table_depend_staging (referencing_database_name, referencing_schema_name, referencing_entity_name, referencing_object_type, referencing_object_type_desc,
      referenced_database_name, referenced_schema_name, referenced_entity_name)
      exec sp_executesql @sql

      fetch next from curs into @dbname
end

close curs

deallocate curs

declare curs cursor for
select name
from master.sys.databases

open curs

fetch next from curs into @dbname

/******************************************GET TYPE OF DEPENDENT OBJECTS**************************************/

while @@FETCH_STATUS = 0
begin

      set @sql =  'update dbo.table_depend_staging
      set referenced_object_type = o.type,
      referenced_object_type_desc = o.type_desc
      from dbo.table_depend_staging s
      inner join ' + @dbname + '.sys.objects o on o.name collate SQL_Latin1_General_CP1_CI_AS = s.referenced_entity_name collate SQL_Latin1_General_CP1_CI_AS
      where referenced_database_name = ''' + @dbname + ''''

      exec sp_executesql @sql

      fetch next from curs into @dbname
end

close curs

deallocate curs
go

if exists (select 1 from dbo.sysobjects where name = 'depend_entity_type')
      drop table depend_entity_type
go

create table dbo.depend_entity_type (
depend_entity_type_id int identity(1,1),
depend_entity_type varchar(2) not null,
depend_entity_type_desc varchar(100) not null)

insert into dbo.depend_entity_type (depend_entity_type, depend_entity_type_desc)
select distinct referenced_object_type, referenced_object_type_desc
from dbo.table_depend_staging
where referenced_object_type_desc is not null
union
select distinct referencing_object_type, referencing_object_type_desc
from dbo.table_depend_staging
where referencing_object_type_desc is not null
go

if exists (select 1 from dbo.sysobjects where name = 'depend_entity')
      drop table depend_entity
go

create table dbo.depend_entity (
depend_entity_id int identity(1,1),
depend_entity_type_id int null,
depend_entity_database sysname null,
depend_entity_schema sysname null,
depend_entity_name sysname not null)
go

insert into dbo.depend_entity (depend_entity_type_id, depend_entity_database, depend_entity_schema, depend_entity_name)
select distinct e.depend_entity_type_id, referenced_database_name, referenced_schema_name, referenced_entity_name
from dbo.table_depend_staging s
left outer join dbo.depend_entity_type e on s.referenced_object_type = e.depend_entity_type
union
select distinct e.depend_entity_type_id, referencing_database_name, referencing_schema_name, referencing_entity_name
from dbo.table_depend_staging s
left outer join dbo.depend_entity_type e on s.referencing_object_type = e.depend_entity_type
go

if exists (select 1 from dbo.sysobjects where name = 'depend_entity_relationship')
      drop table depend_entity_relationship
go

create table dbo.depend_entity_relationship (
depend_entity_relationship_id int identity(1,1),
referencing_id int not null,
referenced_id int not null,
reference_not varchar(1024) null)

insert into dbo.depend_entity_relationship (referencing_id, referenced_id)
select e.depend_entity_id as referencing_id, e2.depend_entity_id as referenced_id
from dbo.table_depend_staging s
inner join dbo.depend_entity e on e.depend_entity_database = s.referencing_database_name
      and e.depend_entity_schema = s.referencing_schema_name
      and e.depend_entity_name = s.referencing_entity_name
inner join dbo.depend_entity e2 on e2.depend_entity_database = s.referenced_database_name
      and e2.depend_entity_schema = s.referenced_schema_name
      and e2.depend_entity_name = s.referenced_entity_name

select r.depend_entity_relationship_id,
e_referencing.depend_entity_database + '.' + e_referencing.depend_entity_schema + '.' + e_referencing.depend_entity_name as referencing_object,
t_referencing.depend_entity_type_desc as referencing_object_type,
e_referenced.depend_entity_database + '.' + e_referenced.depend_entity_schema +  '.' + e_referenced.depend_entity_name as referenced_object,
isnull(t_referenced.depend_entity_type_desc, 'REFERENCED OBJECT DOESN''T EXIST') as referenced_object_type
from dbo.depend_entity_relationship r
inner join dbo.depend_entity e_referenced on e_referenced.depend_entity_id = r.referenced_id
inner join dbo.depend_entity e_referencing on e_referencing.depend_entity_id = r.referencing_id
inner join dbo.depend_entity_type t_referencing on t_referencing.depend_entity_type_id = e_referencing.depend_entity_type_id
left outer join dbo.depend_entity_type t_referenced on t_referenced.depend_entity_type_id = e_referenced.depend_entity_type_id


Thursday, July 3, 2014

Create a list of all FK's with a comma delimited list of columns in SQL Server

Running the query below will create a list of all Foreign Keys and a comma delimited list off all the fields in the relationship.  You'll want to be in the database (i.e. use a "use database" statement).



select t.name as parent_table,
c.name as child_table,
fk.name as foreign_key,
      STUFF(( SELECT  ','+ col.name
            FROM sys.columns col
            inner join sys.foreign_key_columns fkc on fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
            WHERE col.object_id = t.object_id
            FOR XML PATH('')),1 ,1, '')  as foreign_key_fields
from sys.objects o
inner join sys.objects t on t.object_id = o.parent_object_id
inner join sys.foreign_keys fk on o.object_id = fk.object_id
inner join sys.objects c on fk.referenced_object_id = c.object_id
group by t.name, c.name, fk.name, t.object_id
order by fk.name