In this installment we are looking at assembling a basic data dictionary from the column level meta data stored in SQL Server. This is a little different from the rest. You can still do quite a bit with the information provided but to leverage this to the fullest requires a bit of developer buy in. Or a bit more work for whomever is your schema designer. Extended properties have been available to you since the stone ages of SQL Server. Two fine fellows Mark Chaffin and Brian Knight(blog|twitter) expound on the power of extended properties in the article on Managing Metadata in SQL Server 2005. Well worth the read. Using a couple of stored procedures you can store some very rich metadata about every object in your database.

Here are some examples of how to add descriptions to your objects.

–Adding a description to a table
EXEC sp_addextendedproperty
@name = N’MS_Description’,
@value = N’New Description for the Server table’,
@level0type = N’SCHEMA’, @level0name = dbo,
@level1type = N’TABLE’, @level1name = Server;

GO

–adding a description to a column
EXEC sp_addextendedproperty
@name = N’MS_Description’
,@value = N’Login is trusted’
,@level0type = N’Schema’, @level0name = dbo
,@level1type = N’Table’,  @level1name = Server
,@level2type = N’Column’, @level2name = Trusted;
GO
–updating that column description
EXEC sp_updateextendedproperty
@name = N’MS_Description’
,@value = N’Login is windows authenticated’
,@level0type = N’Schema’, @level0name = dbo
,@level1type = N’Table’,  @level1name = Server
,@level2type = N’Column’, @level2name = Trusted;

Pretty simple, yet I’ve always received push back from both developers and database administrators. To me knowledge is power and building the documentation directly into the database metadata is an extremely powerful tool to have handy. At any point I can query sys.extended_properties on an object and find its usage, without having to guess by the object name what it may, or may not actually represent.

A second use case for having the metadata and a history of changes pretty straight forward, checking for schema changes. If you don’t want to implement DDL triggers or use a third party tool to watch for schema changes this is another handy tool in your tool belt. I’ve used this data to track schema deployed across servers to make sure that they are always in sync and if they are out of sync to send an alert.

Couple of quick notes for you. When you are querying sys.extended_properties minor_id is the column_id if you are looking at a class of 1. See sys.extended_properties on technet.

This one is a two in one enjoy!

Series to Date SQLDIY: Manage and Monitor SQL Server Yourself

Link to the script Gather Table Level Metadata

Link to the script Gather Database Level Metadata

I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.

As always, if you find any bugs please let me know and I will correct them!

I am a fan of information, the more the merrier. Anytime I’m tasked with tracking a production server over the long haul I try and gather as many data points as I can. You can capture database sizes but when your database blows up in size its nice to be able to drill down and find out if it was a new index or correlates with a change in code at a point in time. In this script I’m falling back on sp_spaceused to gather that information. I could query several DMV’s to get the same or more information. I could also put in DDL triggers for tracking things like schema changes if I need to the minute and alerting to go along with it. This particular view is more of a “close enough” look at your database in some detail. As we get into more detail index tracking you will see some more specific numbers on row counts, index size and other index specific details. You may see a pattern emerging here. My goal is to gather data in a timely and contestant fashion. We will also gather more detailed meta data on schema later as well.

Series to Date SQLDIY: Manage and Monitor SQL Server Yourself

Link to the script Gather Table Statistics

I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.

As always, if you find any bugs please let me know and I will correct them!

UPDATE:

Aaron Bertrand sugested that I take a look at sys.dm_db_partition_stats instead of sp_spaceused and as usual he was spot on. By moving to sys.dm_db_partition_stats I was able to cut the table loop out completely and sped up the performance quite a bit. New version is up.

Continuing to build up our data gathering library we shift our focus to tracking database file growth. This particular procedure compiles both data and log file usage. This is a fundamental aspect of tracking growth over time and being proactive about growing your databases yourself instead of letting the auto grow making  a mess of your databases. There is no clean and easy way to do this without temp tables so I just have to suck it up and use them. I considered going with a CLR routine and gather more data but we are assuming that you, the user, don’t have the CLR or OLE automation enabled. Personally, I wouldn’t have OLE automation enabled if at all possible. It is another surface area to secure and another source of odd memory issues. There are similar issues with the CLR. Other than the security concerns when there is memory pressure the CLR is ejected and may not be able to load again until the SQL Server process is recycled. With all that in mind I present to you Database Space Detail schema and script!

Series to Date SQLDIY: Manage and Monitor SQL Server Yourself

Link to the script Gather Database Space Detail

I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.

As always, if you find any bugs please let me know and I will correct them!