Steve Fibich : Meta Data Management, Obscure T-SQL

space_used_sp vs. sp_spaceUsed

steve — Tue, 08 Jul 2008 12:07:00 GMT

Ok I'm not one to re-write standard system stored procedures for SQL Server. That being said I have been working a good deal with files and file groups within SQL Server over the past few days moving data and indexes to files and file groups other than the default PRIMARY file group. Sp_spaceUsed is great procedure for giving you overall storage utilization information for a database or database objects (table, index view, or queue). As I have been moving indexes and tables over to different file groups I wanted to see how this was splitting my data as far as size and row counts was going and how it was being disturbed across the file system. Unfortunately sp_spaceUsed does not give you this level of detail and I could not find a system view or other system procedure to give me all of the information that I wanted.

sp_spaceUsed

So I set out to create my own and now that I have it working and figured I'll share it with everyone. I ended up creating two objects, one a view sp_space_used_view and the second a procedure space_used_sp. I’m a database guy so I love organization so I put both of these objects into a schema named metadata that is owned by DBO. The view displays the space used by all objects in the database with rollup levels being displayed as ' -db-level', ' -schema-level', and ' -table-level'. The view relies upon the sys.objects (due to the fact that system tables are not in sys.tables so I was losing the space and row count information for these objects.), sys.indexes, sys.partions, sys.database_files, sys.schemas, sys.dm_db_partion_stats, sys.internal_tables, and sys.index_columns catalog views; all of which have detail coverage in BOL. The view utilizes these system catalog views to show rows counts, reserved space, data space, index space, and unused space in the database files broken down by table. I figure I would want to see all of the detailed data for a given database sometimes, rollups others, and individual objects separately as well so encapsulation of the select logic into a view allows re-use of this code in other objects. The procedure utilizes the view to show you information for a particular table or for the overall database. I hope this code is useful to others as well. One of the main differences between sp_spaceUsed and my space_used_sp is that sp_spaceUsed allows for the updating of system information which my procedure does not, it only returns data to the screen. The second difference is that sp_spaceUsed requires the schema name to be passed as part of the table name if you want to view information on an object outside of your default schema, space_used_sp does not require this and displays all objects with the name you provide giving you the schema name as part of the output.

space_used_sp vs. sp_spaceUsed

Here it is: (Note:everything is created in a schema call metadata)

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[metadata].[space_used_vw]'))
DROP VIEW [metadata].[space_used_vw]
go
create view metadata.space_used_vw
-------------------------------------------
--SFIBICH
--7/7/2008
--The purpose of this view is to show space used broken
--down by SQL Server data files. Currently is does not calculate
--space for fulltext indexes and or XML indexes.
--
-------------------------------------------
as
select
top 100 PERCENT
coalesce([schema_name],' -db level-') schemaName
,coalesce(table_name,' -schema level-') tableName
,coalesce(data_file_name,' -table level-') dataFileName
,[rowCount]
,reserved*8 reservedKB
,data*8 dataKB
,case when used_page_count>data then (used_page_count-data)*8 else 0 end indexKB
,case when reserved>used_page_count then (reserved-used_page_count)*8 else 0 end unusedKB
from
(
SELECT
t.name table_name
,df.name data_file_name
,s.name [schema_name]
,SUM(
    CASE
  WHEN (p.index_id < 2) THEN row_count
  ELSE 0
    END
) [rowCount]
,SUM(reserved_page_count)+coalesce(max(IT_reserved_page_count),0) reserved
,SUM(
    CASE
  WHEN (p.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
  ELSE lob_used_page_count + row_overflow_used_page_count
    END
)data
,SUM(used_page_count)+coalesce(max(IT_used_page_count),0) used_page_count
FROM sys.dm_db_partition_stats p
inner join
sys.objects t
on
p.object_id=t.object_id
inner join
sys.schemas s
on
t.schema_id=s.schema_id
inner join
sys.indexes i
on
p.object_id=i.object_id
and
p.index_id=i.index_id
inner join
sys.database_files df
on
i.data_space_id=df.data_space_id
left outer join
(--Internal tables are placed on the same filegroup as the parent entity. (BOL)
  select
  pp.object_id
  ,min(pp.index_id) index_id
  ,sum(p.reserved_page_count) IT_reserved_page_count
  ,sum(p.used_page_count) IT_used_page_count
  FROM
  sys.dm_db_partition_stats p
  inner join
  sys.internal_tables it
  on
  p.object_id = it.object_id
  inner join
  sys.dm_db_partition_stats pp
  ON
  it.parent_object_id=pp.object_id
  WHERE
  it.internal_type IN (202,204)
  group by
  pp.object_id
)it
on
p.object_id=it.object_id
and
p.index_id=it.index_id
group by
s.name
,t.name
,df.name
with rollup
) dataTable
order by
[schema_name]
,table_name
,[rowCount] desc
,data_file_name asc
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[metadata].[space_used_sp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [metadata].[space_used_sp]
go
create procedure metadata.space_used_sp
-------------------------------------------
--SFIBICH
--7/7/2008
--This is a replacement for sp_spaceused, it utlizes the view
--[metadata].[space_used_vw]. Currently it only reports on tables
--and the total for tables in a schema, or database level broken
--down by database files.
--
-------------------------------------------
@tableName sysname =null
as
if @tableName is null
begin
--------------------
--
--database, schema info
--
--------------------
select
' -db level-' schemaName
,' -schema level-' tableName
,dataFileName
,sum([rowCount]) [rowCount]
,sum(reservedKB) reservedKB
,sum(dataKB) dataKB
,sum(indexKB) indexKB
,sum(unusedKB) unusedKB
from
[metadata].[space_used_vw]
where
dataFileName <>' -table level-'
or
(
dataFileName =' -table level-'
and
schemaName=' -db level-'
and
tableName =' -schema level-'
)
group by
dataFileName
order by [rowCount] desc,dataFileName
end
else
begin
--------------------
--
--particular table
--
--------------------
select
schemaName
,tableName
,dataFileName
,[rowCount]
,reservedKB
,dataKB
,indexKB
,unusedKB
from
[metadata].[space_used_vw]
where
tableName =@tableName
end

Data Dictionary Part II

steve — Fri, 25 Jan 2008 17:26:00 GMT

Ok, after putting up my first post on the Data Dictionary I had posters remorse. There is a simple change that makes the script shown in the previous post a little more user friendly. First the view metadata.columnsDataDictionary need to be changed from a SQL Select using a pivot to just a self join from the metadata.columnsExtendedProperties to allow for columns that shared the same domain to easily display different MS_Description values. The second change was to allow the procedure metadata.columnsExtendedPropertiesRepopulate to allow for an overall overwrite or just to add new values and retain old MS_Description extended propertie values. I don't think I mentioned this earlier the extended property of 'MS_Description' will be picked up by a number of development applications so it is useful to stick your data dictionary value in there but not to overwrite one if it came supplied with say a third part application installation or if you are allowing a third party applicaiton manage your descriptioins in the database.

Well thats it here is the code with a simple example:

USE [steves]

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedProperties]'))

DROP VIEW [metadata].[columnsExtendedProperties]

IF EXISTS (SELECT * FROM sys.views

WHERE object_id = OBJECT_ID(N'[metadata].[columnsDataDictionary]'))

DROP VIEW [metadata].[columnsDataDictionary]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[metadata].[dataDictionary]') AND type in (N'U'))

DROP TABLE [metadata].[dataDictionary]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[testExtendedProperties]') AND type in (N'U'))

DROP TABLE [dbo].[testExtendedProperties]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedPropertiesRepopulate]') AND type in (N'P', N'PC'))

DROP PROCEDURE [metadata].[columnsExtendedPropertiesRepopulate]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [metadata].[dataDictionary](

[domainName] [varchar](50) NOT NULL,

[domainDesc] [varchar](1000) NOT NULL,

PRIMARY KEY CLUSTERED

(

[domainName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF

, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

exec sp_addextendedproperty 'domain', 'domainName'

, 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainName'

exec sp_addextendedproperty 'domain', 'domainDesc'

, 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainDesc'

insert into metadata.dataDictionary

(domainName,domainDesc)

values('domainName','Short text name for the classification of a businessdata type or attribute')

insert into metadata.dataDictionary

(domainName,domainDesc)

values('domainDesc','Description of the a business data type or attribute')

create view [metadata].[columnsExtendedProperties]

----------------------------------------------

--Steve Fibich

--1.11.2007

--This view is used to match columns to their extended

--properties. This is an extension of sys.extended_properties

--system view.

----------------------------------------------

select

top 100 percent

s.name as schemaName

,t.name as TableName

,c.name as columnName

,e.name as EName

,e.value as EDesc

,c.column_id as columnID

from

sys.extended_properties e

inner join

sys.tables t

t.object_id=e.major_id

inner join

sys.columns c

e.minor_id=c.column_id

and

e.major_id=c.object_id

inner join

sys.schemas s

t.schema_id=s.schema_id

where

class=1

order by

s.name

,t.name

,c.column_id

,e.name

create view [metadata].[columnsDataDictionary]

----------------------------------------------

--Steve Fibich

--1.11.2007

--This view is used to match columns to their extended

--properties. It does this for particular extended properties

--"domain" and "MS_Description. It then pivots these results for a

--more useful layout.

----------------------------------------------

select

top 100 percent

cep1.schemaName

,cep1.tableName

,cep1.columnName

,cep1.eDesc as domain

,cep2.eDesc as MS_Description

from

(

select

schemaName

,tableName

,columnName

,eDesc

,eName

,columnId

from [metadata].[columnsExtendedProperties]

where

EName='domain'

) cep1

full outer join

(

select

schemaName

,tableName

,columnName

,eDesc

,eName

,columnId

from

[metadata].[columnsExtendedProperties]

where

EName='MS_Description'

) cep2

cep1.schemaName=cep2.schemaName

and

cep1.tableName=cep2.tableName

and

cep1.columnID=cep2.columnID

order by

cep1.schemaName

,cep1.tableName

,cep1.columnID

create proc metadata.columnsExtendedPropertiesRepopulate

----------------------------------------------

--Steve Fibich

--1.11.2007

--This proc removes any 'MS_Description' extended properties

--from any columns of any tables that have matching entires in the

--metadata.datadictionary table and that have an extended propertie of 'domain'

--This is to try to make keeping 'MS_Description' descripions upto date eaiser

--@overwrite=0 turns off overwriting so existing MS_Descriptions will be left alone

--@overwrite=1 existing MS_Description values will be deleted and re-created

--@debug =0 sets additional logging off

--@debug=1 sets additional looging on

----------------------------------------------

@overwrite bit=0

,@debug bit=0

--if @debug=0

-- BEGIN

-- select @debug=debug from metadata.logging where schemaName='metadata'

-- END

declare @schemaName sysname

declare @tableName sysname

declare @columnName sysname

declare @domainDesc varchar(1000)

declare extProp_cursor cursor for

select

e.schemaName

,e.tableName

,e.columnName

,dd.domainDesc

from

metadata.dataDictionary dd

inner join

[metadata].[columnsExtendedProperties] e

dd.domainName=edesc

where

ename='domain'

open extProp_cursor

Fetch next from extProp_cursor into

@schemaName,@tableName,@columnName,@domainDesc

while @@fetch_status=0

BEGIN

if @debug=1

BEGIN

select @schemaName,@tableName,@columnName,@domainDesc

END

if (exists (select * from ::fn_listextendedproperty('MS_Description', 'schema'

, @schemaName, 'table', @tableName, 'column', @columnName)) and (@overwrite=0))

BEGIN

execute sp_dropextendedproperty 'MS_Description','schema'

,@schemaName,'table',@tableName,'column',@columnName

END

if not exists (select * from ::fn_listextendedproperty('MS_Description', 'schema'

, @schemaName, 'table', @tableName, 'column', @columnName))

BEGIN

execute sp_addextendedproperty 'MS_Description',@domainDesc,'schema'

,@schemaName,'table',@tableName,'column',@columnName

END

Fetch next from extProp_cursor into

@schemaName,@tableName,@columnName,@domainDesc

END

close extProp_cursor

deallocate extProp_cursor

-----------------------------------------

--Example Table and Extended Properties--

-----------------------------------------

create table dbo.testExtendedProperties

(

column1 int

,column2 int

,column3 int

,column4 int

,column5 int

,column6 int

)

exec sp_addextendedproperty 'domain', 'domainName'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column1'

exec sp_addextendedproperty 'domain', 'domainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column2'

exec sp_addextendedproperty 'domain', '3rdDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column3'

exec sp_addextendedproperty 'domain', '4thDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column4'

exec sp_addextendedproperty 'domain', '3rdDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column5'

exec sp_addextendedproperty 'domain', '4thDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column6'

insert into metadata.dataDictionary

(domainName,domainDesc)

values('3rdDomainDesc','This is the 3rd domain description that we have it should be on column3 and column5')

insert into metadata.dataDictionary

(domainName,domainDesc)

values('4thDomainDesc','This is the 4th domain description that we have it should be on column4 and column6')

execute metadata.columnsExtendedPropertiesRepopulate

select * from [metadata].[columnsDataDictionary]

select * from [metadata].[columnsExtendedProperties]

Data Dictionary part I

steve — Tue, 15 Jan 2008 17:26:00 GMT

Having a data dictionary is an invaluable resource for not only for the DBA but for anyone, report writers or developers who need to work on a database that you have to manage or have developed yourself. A good amount of time can be saved from answering the same questions over and over again if you can point to documentation to answer any initial questions. Microsoft provides a way to keep a this information in each database without having to change the structure of any of the objects that already exist. Extended Properties are a valuable resource in keeping metadata close to the heart of the system where it is needed. In this series I will show you my data dictionary frame work. I do create one table data dictionary that contains information that will work in conjunction with MS extended properties system functions, procedures, and views. I have a concept of "data domains" which is a way to relate different columns in the same table or different tables that contain the same logical information that may not support a foreign key relationship. An example of this is state in a data warehouse or master data management system, some tables may use the USPS two letter code, other tables from a different system may use an ISO 3 digit numeric code, but in each of these cases they represent the same information. Connecting these two different data elements in the database can be incredibly useful to a developer especially if they are new to your environment. To facilitate this I create an extended property every column in the database that is "domain" and populate it with the name of the data domain, in the example above the extended properties "domain" would get a value of "state" in each of the two different tables. I then populate my dataDictionary table with the domain "state" and a description for this domain. I have simple stored procedure that will read my dataDictionary table and place all of the domain descriptions in a extended propriety of "MS_Description" for all columns that have a matching value in the "domain" extended property. I then have two simple views that extended the sys.extended_properites system view (SQL 2005 object). Below I have listed the code that will generate the aforementioned objects and a quick sample to show what it can do. (This code requires a metadata schema to exist)

IF EXISTS (SELECT * FROM sys.views

WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedProperties]'))

DROP VIEW [metadata].[columnsExtendedProperties]

IF EXISTS (SELECT * FROM sys.views

WHERE object_id = OBJECT_ID(N'[metadata].[columnsDataDictionary]'))

DROP VIEW [metadata].[columnsDataDictionary]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[metadata].[dataDictionary]') AND type in (N'U'))

DROP TABLE [metadata].[dataDictionary]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[testExtendedProperties]') AND type in (N'U'))

DROP TABLE [dbo].[testExtendedProperties]

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id = OBJECT_ID(N'[metadata].[columnsExtendedPropertiesRepopulate]') AND type in (N'P', N'PC'))

DROP PROCEDURE [metadata].[columnsExtendedPropertiesRepopulate]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [metadata].[dataDictionary](

[domainName] [varchar](50) NOT NULL,

[domainDesc] [varchar](1000) NOT NULL,

PRIMARY KEY CLUSTERED

(

[domainName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF

, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

exec sp_addextendedproperty 'domain', 'domainName'

, 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainName'

exec sp_addextendedproperty 'domain', 'domainDesc'

, 'schema', 'metadata', 'table', 'dataDictionary', 'column', 'domainDesc'

insert into metadata.dataDictionary

(domainName,domainDesc)

values('domainName','Short text name for the classification of a businessdata type or attribute')

insert into metadata.dataDictionary

(domainName,domainDesc)

values('domainDesc','Description of the a business data type or attribute')

create view [metadata].[columnsExtendedProperties]

----------------------------------------------

--Steve Fibich

--1.11.2007

--This view is used to match columns to their extended

--properties. This is an extension of sys.extended_properties

--system view.

----------------------------------------------

select

top 100 percent

s.name as schemaName

,t.name as TableName

,c.name as columnName

,e.name as EName

,e.value as EDesc

,c.column_id as columnID

from

sys.extended_properties e

inner join

sys.tables t

t.object_id=e.major_id

inner join

sys.columns c

e.minor_id=c.column_id

and

e.major_id=c.object_id

inner join

sys.schemas s

t.schema_id=s.schema_id

where

class=1

order by

s.name

,t.name

,c.column_id

,e.name

create view [metadata].[columnsDataDictionary]

----------------------------------------------

--Steve Fibich

--1.11.2007

--This view is used to match columns to their extended

--properties. It does this for particular extended properties

--"domain" and "MS_Description. It then pivots these results for a

--more useful layout.

----------------------------------------------

select

top 100 percent

schemaName

,tableName

,columnName

,[domain]

,[MS_Description]

from

(

select

schemaName

,tableName

,columnName

,columnID

,EName

,EDesc

from

[metadata].[columnsExtendedProperties]

PIVOT

(

min(EDesc)

for EName IN

([domain],[MS_Description])

)as pvt

order by

tableName

,columnID

create proc metadata.columnsExtendedPropertiesRepopulate

----------------------------------------------

--Steve Fibich

--1.11.2007

--This proc removes any 'MS_Description' extended properties

--from any columns of any tables that have matching entires in the

--metadata.datadictionary table and that have an extended propertie of 'domain'

--This is to try to make keeping 'MS_Description' descripions upto date eaiser

----------------------------------------------

@debug bit=0

--if @debug=0

-- BEGIN

-- select @debug=debug from metadata.logging where schemaName='metadata'

-- END

declare @schemaName sysname

declare @tableName sysname

declare @columnName sysname

declare @domainDesc varchar(1000)

declare extProp_cursor cursor for

select

e.schemaName

,e.tableName

,e.columnName

,dd.domainDesc

from

metadata.dataDictionary dd

inner join

[metadata].[columnsExtendedProperties] e

dd.domainName=edesc

where

ename='domain'

open extProp_cursor

Fetch next from extProp_cursor into

@schemaName,@tableName,@columnName,@domainDesc

while @@fetch_status=0

BEGIN

if @debug=1

BEGIN

select @schemaName,@tableName,@columnName,@domainDesc

END

if exists (select * from ::fn_listextendedproperty('MS_Description'

, 'schema', @schemaName, 'table', @tableName, 'column', @columnName))

BEGIN

execute sp_dropextendedproperty 'MS_Description','schema'

,@schemaName,'table',@tableName,'column',@columnName

END

execute sp_addextendedproperty 'MS_Description',@domainDesc,'schema'

,@schemaName,'table',@tableName,'column',@columnName

Fetch next from extProp_cursor into

@schemaName,@tableName,@columnName,@domainDesc

END

close extProp_cursor

deallocate extProp_cursor

-----------------------------------------

--Example Table and Extended Properties--

-----------------------------------------

create table dbo.testExtendedProperties

(

column1 int

,column2 int

,column3 int

,column4 int

,column5 int

,column6 int

)

exec sp_addextendedproperty 'domain', 'domainName'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column1'

exec sp_addextendedproperty 'domain', 'domainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column2'

exec sp_addextendedproperty 'domain', '3rdDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column3'

exec sp_addextendedproperty 'domain', '4thDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column4'

exec sp_addextendedproperty 'domain', '3rdDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column5'

exec sp_addextendedproperty 'domain', '4thDomainDesc'

, 'schema', 'dbo', 'table', 'testExtendedProperties', 'column', 'column6'

insert into metadata.dataDictionary

(domainName,domainDesc)

values('3rdDomainDesc','This is the 3rd domain description that we have it should be on column3 and column5')

insert into metadata.dataDictionary

(domainName,domainDesc)

values('4thDomainDesc','This is the 4th domain description that we have it should be on column4 and column6')

execute metadata.columnsExtendedPropertiesRepopulate

select * from [metadata].[columnsDataDictionary]

I will be revising this data dictionary system in future posts, I already have an issue with the way the columnsDataDictionary view functions. As you may have noticed the columnsExtendedPropertiesRepopulate overwrites any already existing "MS_Description" values already in place. This will be enhanced in future versions.