Wednesday, October 8, 2014

List procedures and their dependent tables


distinct as 'Procedure Schema Name', as 'Procedure Name', as 'Table Schema Name', as 'Table Name' from sysdepends d

join sys.objects o1 on = o1.object_id

join sys.schemas s1 on o1.schema_id = s1.schema_id

join sys.objects o2 on d.depid = o2.object_id

join sys.schemas s2 on o2.schema_id = s2.schema_id


o1.type = 'P' and

o2.type = 'U'


by 1,2,3,4

Query to look at filegroups by table

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name

, AS table_name


, AS index_name

, AS filegroup_name


FROM sys.tables t

INNER JOIN sys.indexes i ON t.object_id=i.object_id

INNER JOIN sys.filegroups ds ON i.data_space_id=ds.data_space_id

INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
ORDER BY, i.index_id

Thursday, August 28, 2014

DATABASE Backup Recovery Model and Last Completion Date

select @@servername, * from


-- Query last backup





        MAX(b.backup_finish_date) AS backup_finish_date

FROM     master.sys.databases d

         LEFT OUTER JOIN msdb..backupset b

         ON       b.database_name =

         --AND      b.type          = 'L'

GROUP BY, b.type, d.recovery_model_desc

--ORDER BY backup_finish_date DESC

) a


Tuesday, August 26, 2014

Script to identify index sizing without cross join

SELECT                  AS IndexName,

    SUM(s.used_page_count) * 8   AS IndexSizeKB

FROM sys.dm_db_partition_stats  AS s

JOIN sys.indexes                AS i

ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

WHERE s.[object_id] = object_id('YOURTABLENAMEHERE')




Log file space available

-- (with Mark Nelson)

USE master



Create Table ##temp


    DatabaseName sysname,

    Name sysname,

    physical_name nvarchar(500),

    FileSizeMB decimal (18,2),

                SpaceUsedMB decimal (18,2),

    FreeSpaceMB decimal (18,2)


Exec sp_msforeachdb '

Use [?];

Insert Into ##temp (DatabaseName, Name, physical_name, FileSizeMB, SpaceUsedMB, FreeSpaceMB)

    Select DB_NAME() AS [DatabaseName], Name,  physical_name,

    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) FileSizeMB,


    Cast(Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) as SpaceUsedMB,


    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -

        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpaceMB


    From sys.database_files


Select * From ##temp WHERE right(physical_name,3) = 'ldf'

drop table ##temp

Shrink all the logs on a server

-- (in conjunction with Mark Nelson)



SET @cmd = 'USE [?] ;


declare @filename varchar(1000), @dbcc varchar(2000)

select @filename = name from sys.sysfiles where filename like ''%ldf''

print @filename

set @dbcc = ''dbcc shrinkfile (xx, 1000)''

set @dbcc = REPLACE(@dbcc, ''xx'' ,@filename)

exec (@dbcc)



Exec sp_msforeachdb @cmd

Code to remove a single plan from cache

-- Remove one plan from the cache

-- Get the plan handle for a cached plan

SELECT cp.plan_handle, st.[text]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE [text] LIKE N'%YourProcNameHere%';


-- Remove the specific plan from the cache using the plan handle

DBCC FREEPROCCACHE (0x06001C00FCA2651EF08667E42A00000001000000000000000000000000000000000000000000000000000000);