Wednesday, October 8, 2014

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

Thursday, August 28, 2014

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

 

Tuesday, August 26, 2014

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