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

Cleaning up a replicated database following a restore.

If you ever receive the error: "Cannot truncate table 'TABLENAME' because it is published for replication or enabled for Change Data Capture." but your database is not set up for replication it could be that the individual tables still think they're being replicated.  To see if this is the case run this query:
select * from sys.objects where is_published = 1
If you get any results then those tables think they are replicated.  To remove this replication run:
exec sp_removedbreplication 'DATABASENAME'
Warning!!  Verify you actually want to remove replication before doing this, once its gone it's gone and you'll need to resync.

I've noticed this behavior on occassion after backup up a replicated database and restoring it.  The restored version still has these flags set.

Cheers.

Part Time Database Administration

As many of you know I started a new part time database administration company a few months back and its been going great.  If you're in the need for a database administrator but can't justify hiring an FTE please check us out.  http://www.yourparttimedba.com/p/part-time-sql-server-database.html

-Jeff

How to tell what tables in a database are compressed

Run this query and then run the output of the query (remember to drop off the last union).


select 'select''' + name + ''' as dbnme, st.name, st.object_id, sp.data_compression_desc FROM ' + name + '.sys.partitions sp INNER JOIN ' + name + '.sys.tables st ON st.object_id = sp.object_id WHERE data_compression <> 0 union'
from master.sys.databases