Tuesday, February 3, 2015

How big are the tables in my databases?

I stole this code, and put it here so that I could find it. Have fun.

SELECT

t.NAME AS TableName,

s.Name AS SchemaName,

p.rows AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE

t.NAME NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name, p.Rows

ORDER BY

TotalSpaceKB desc




-- t.Name

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