intergr8 it

Tabulate the table sizes and row counts in a SQL 2013 DB

SELECT
s.name + ‘.’ + t.Name AS [Table Name],
t.object_id as [Object Id],
part.rows AS [Total Rows In Table – Modified],
CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3))
AS [Table’s Total Space In GB]
FROM
SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id
INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id
AND idx.Index_id = part.Index_id
INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id
INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id
INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id
WHERE t.Is_ms_shipped = 0 AND idx.Object_id > 255
GROUP … Continue Reading

As maintainers of a number of client databases we come across issues in automating updates and changes to their SQL Server database schemas.  One such issue revolves around the automatic naming of CONSTRAINTS.

If you create a CONSTRAINT, such as a Primary Key, Foreign Key or even a Default without explicitly givin a name, SQL will generate its own unique name for the CONSTRAINT.

This is handy since its a unique name in the database, but its also unique across all databases so for clients with a hosted application, automated upgrades to each application database can be tricky.

This script below is a generic way … Continue Reading