List procedures and their dependent tables

select

distinct s1.name as 'Procedure Schema Name', o1.name as 'Procedure Name', s2.name as 'Table Schema Name', o2.name as 'Table Name' from sysdepends d


join sys.objects o1 on d.id = 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


where


o1.type = 'P' and


o2.type = 'U'

order

by 1,2,3,4

Query to look at filegroups by table

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name

,t.name AS table_name

,i.index_id

,i.name AS index_name

,ds.name AS filegroup_name

,p.rows

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 t.name, i.index_id

DATABASE Backup Recovery Model and Last Completion Date

select @@servername, * from

(

-- Query last backup

SELECT  

              d.name,

              d.recovery_model_desc,

              b.type,

        MAX(b.backup_finish_date) AS backup_finish_date

FROM     master.sys.databases d

         LEFT OUTER JOIN msdb..backupset b

         ON       b.database_name = d.name

         --AND      b.type          = 'L'

GROUP BY d.name, b.type, d.recovery_model_desc

--ORDER BY backup_finish_date DESC

) a

 

Script to identify index sizing without cross join

SELECT

    i.name                  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')

GROUP BY i.name

ORDER BY i.name

 

Log file space available

-- (with Mark Nelson)

USE master

GO

 

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)

DECLARE @cmd VARCHAR(6000)

 

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);