Table Analysis Made Easy

Run this query in your database to get a list of all tables, comma delimited lists of what they reference, comma delimited list of what references them, row count, and total number of stored procedures and function which reference them.

declare @t table (

dbname sysname not null,

tablename sysname not null,

records int not null,

childtables varchar(max) null,

parenttables varchar(max) null,

refcount int null)

declare @tablename sysname

declare @refcount int

insert into @t (dbname, tablename, records, childtables, parenttables)

select DB_NAME(), t.name, i.rowcnt,

STUFF(( SELECT ',' + c.name

      from sys.foreign_keys fk

      inner join sys.objects c on fk.parent_object_id = c.object_id

      where t.object_id = fk.referenced_object_id

      FOR XML PATH('')),1 ,1, '') as children,

STUFF(( SELECT ',' + c.name

      from sys.foreign_keys fk

      inner join sys.objects c on fk.referenced_object_id = c.object_id

      where t.object_id = fk.parent_object_id

      FOR XML PATH('')),1 ,1, '') as parents

from sys.objects t

inner join sysindexes i on t.object_id = i.id

where t.type = 'u'

and t.name <> 'dtproperties'

and t.name not like 'mspeer%'

and t.name not like 'mspub%'

and t.name not like 'sys%'

and i.indid in (0, 1)

order by t.name

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

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

Query to Get Line Count of a Stored Procedure in SQL Server

The query below will return a programming object along with the number of carriage returns which exist within it.

select t.sp_name, sum(t.lines) - 1 as lines_of_code, type

from

      (select o.name as sp_name, o.type,

    (len(c.text) - len(replace(c.text, char(10), ''))) as lines

    from sysobjects o

    inner join syscomments c on c.id = o.id

    where o.type not in ('c', 'd')) t

group by t.sp_name, type

Get a cascading list of all tables that have a foreign key to a table or any of its dependencies

declare @t table (

foreign_key_name sysname not null,

foreign_table sysname not null,

parent_table sysname not null,

checked bit default 0)

declare @table_to_check as sysname

set @table_to_check = 'table_name'

insert into @t (foreign_key_name, foreign_table, parent_table, checked)

select f.name as foreign_key_name,

c.name as foreign_table,

p.name as parent_table,

case when p.name = c.name then 1 else 0 end

from  sysobjects f

inner join sysobjects c on f.parent_obj = c.id

inner join sysreferences r on f.id = r.constid

inner join sysobjects p on r.rkeyid = p.id

where f.type = 'F'

and p.name = @table_to_check

while exists (select 1 from @t where checked = 0)

begin

      set @table_to_check = (select top 1 foreign_table from @t where checked = 0)

      insert into @t (foreign_key_name, foreign_table, parent_table, checked)

      select distinct f.name as foreign_key_name,

      c.name as foreign_table,

      p.name as parent_table,

      case when p.name = c.name then 1 when t.parent_table is null then 0 else 1 end as checked

      from  sysobjects f

      inner join sysobjects c on f.parent_obj = c.id

      inner join sysreferences r on f.id = r.constid

      inner join sysobjects p on r.rkeyid = p.id

      left outer join @t t on t.parent_table = c.name

      where f.type = 'F'

      and p.name = @table_to_check

      update @t set checked = 1 where foreign_table = @table_to_check

end

select *

from @t

order by foreign_table

Get All Query Plans & Performance Stats in Cache for a Stored Procedure

Replace "DATABASE" with the database name and "STORED PROCEDURE NAME" with the stored procedure and run.

use DATABASE

go

SELECT s.object_id, s.cached_time, s.sql_handle, s.plan_handle, s.last_execution_time, s.execution_count, s.total_worker_time,

s.min_worker_time, s.max_worker_time, s.total_physical_reads, s.min_physical_reads, s.max_physical_reads, s.total_logical_reads, s.min_logical_reads,

s.max_logical_reads, s.total_logical_writes, s.min_logical_writes, s.max_logical_writes, s.total_elapsed_time, s.min_elapsed_time, s.max_elapsed_time

FROM sys.procedures AS p

INNER JOIN sys.dm_exec_procedure_stats AS s ON s.object_id = p.object_id

WHERE p.name = 'STORED PROCEDURE NAME'

and s.database_id = DB_ID()

Disable all snapshots on a distributor

When creating replication from script I find that the snapshot agents are enabled and scheduled to run on the hour.  To prevent them from pounding the database and running you need to disable them.  If you have a lot of snapshot agents disabling them all through the GUI can take a long time.  The script below will disable all snapshot agents.


declare @sql nvarchar(1000)

declare curs cursor for
select 'EXEC msdb.dbo.sp_update_job @job_id=N''' + cast(job_id as varchar(100)) + ''', @enabled=0'
from msdb.dbo.sysjobs where date_created > '12/03/2013' and enabled = 1 and category_id = 15 --snapshot'

open curs

fetch next from curs into @sql

while @@FETCH_STATUS = 0
begin
print @sql

exec sp_executesql @sql

fetch next from curs into @sql
end

close curs

deallocate curs

Coin of the month club

I don't normally post personal stuff, but my kids have started up a coin of the month club business.  Subscribe and get a new coin delivered to your house each month.  Check it out.

http://threebrotherscoins.weebly.com/index.html