sp_spaceused Alternative

Andrew Kelly — Fri, 17 May 2013 20:35:00 GMT

I don’t know why but for some reason I have never liked using sp_spaceused. It probably started a long time ago when the results were often inaccurate due to the meta data being out of date. I am pretty sure that was fixed somewhere along the line but the system stored procedure had some other limitations that usually prevented me from seeing the data that I really wanted in most cases. So at some point I created my own stored procedure to view the data I was most interested in almost on a daily occurrence. Now to be totally accurate I started with code that one of the SSMS reports uses to show data and index space usage. The code was not the prettiest so I did clean it up a little and modified it to better suite my needs but the core worked so I used it. I then added some additional queries to get other data that the original query didn’t have such as the file size and growth info. I also slightly modified one of the many variations of functions available out there that format numbers with commas for better readability. In this case it is simply called dbo.commas. Normally I have a database on every instance in which I keep custom procedures / functions etc. in which I can count on certain objects to exist so I can reference them in other objects. These examples are shown with no fully qualified path to the dbo.commas UDF so the UDF and the procedure would have to exist in each database or the one that you care to use it in. While that works you should consider doing one of two alternatives. Either add a utility database like I mentioned and put the two objects there or rename the procedure to have sp_xxx and put the objects in the master database and then mark them as system objects. That way you can call the procedure from the context of any database.

OK so enough about that lets see what the procedure does and how to use it. The procedure has two parameters @DBName and @ShowFileInfo. The @DBName parameter is pretty self explanatory and will dictate which database the results are for. The second parameter is really optional as it defaults to a value = 0. If the @ShowFileInfo parameter = 0 then only one result set will be returned which contains the relevant data for each table in the database. More on that in a bit. If a value of 1 is passed in then a 2nd result set is returned that contains information related to the size of the database, it’s files and growth. See further down for more details on both result sets. These are all acceptable ways to call the stored procedure:

EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks'
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 0
EXEC dbo.ListDBSpaceInfo @DBName = 'Adventureworks', @ShowFileInfo = 1
EXEC dbo.ListDBSpaceInfo 'Adventureworks'
EXEC dbo.ListDBSpaceInfo 'Adventureworks', 1

Here is what the results would look like if you ran it against the Adventureworks database.

The first result set lists each table showing the size in MB’s for the reserved, data and index space. The row counts and the Created and Modified dates. This is the information I find most useful the majority of the time. It orders the tables by Reserved MB but you may prefer by table name or something else. The code is easy enough to modify to sort however you like and you can even add an additional parameter to chose the sort by column. Again I use it this way most often so it is what it is.

The 2nd result set if you choose to return it will list certain information about the database and it’s files. It will show the current total size of all the data files which is pretty definitive by nature. However the next few columns are dependent on the max size and growth properties of each file. For instance if any of the data files are set to –1 which means unlimited growth the resultant column for Max Size will say Unlimited as will the Potential Free Space. If the files have a fixed size the total max size if then calculated and displayed along with that amount minus the current data usage to give you the potential free space. Basically that is how much free space the database can theoretically accommodate assuming there is enough space on the disks. The same holds true for the log file. These numbers are most useful if you have limits set but the information can give you a good idea of the overall condition of the data and log file usage.

Now I understand that none of this is information that wasn’t available to users before but I usually had to go to several different sources to get a comprehensive view like this. The purpose of this blog post was not to wow everyone with some rocket science code it was simply to share with others who may find it useful. I use it all the time so I figure there must be others who can take advantage of it as well and hopefully customize it to suite their needs like I have. Have fun everyone, here is the code:

Andy

Cleaning up Un-Named Defaults

Andrew Kelly — Fri, 26 Jun 2009 16:07:00 GMT

I just had a situation in which we are automating the comparing of databases and I came across a bunch of Defaults that were never explicitly named. This causes them to get a new name each time you create them and makes it much more difficult to write future update scripts when you don’t know the actual name it will be. In any case I decided to clean them up and since there were about a hundred I didn’t want to create all the cleanup code by hand and wrote a little script to generate the DROP & ADD’s which I figured others may find useful as well. The code would look much better if Windows Live Writer would keep the rich text when pasting into it but it doesn’t by default. There are some plug in’s to allow this but I have had little success so far in getting ones to install that actually do what I want. I will just have to deal with that later when I have more time.

The code below simply adds the necessary meta-data to the temp table that I need to Drop and Add the default constraints. I then run a select statement that dynamically builds each Drop or Add using that data and the resulting text can be used as a script to do the cleanup. It’s pretty simple stuff but may save some typing for anyone else wishing to cleanup messes like this that lazy developers :) and SSMS create. In my opinion ALL objects should be explicitly named at the time of initial creation and nothing should be left to be auto-generated by the tool or the engine. It will only lead to heartache down the road. And as always, test any code you find here first…

SET NOCOUNT ON ;

CREATE TABLE #DF ([Table Name] NVARCHAR(128), [Column Name] NVARCHAR(128),
[Default Name] NVARCHAR(128), [definition] NVARCHAR(200)) ;

INSERT INTO #DF ([Table Name], [Column Name], [Default Name], [definition] )
SELECT object_name(d.[parent_object_id]) AS [Table Name], c.[name] AS [Column Name],
d.[name] AS [Default Name], d.[definition]
FROM sys.columns AS c INNER JOIN sys.default_constraints AS d
ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
AND d.is_system_named = 1

-- Drop the existing constraints
SELECT 'DECLARE @DF_Name NVARCHAR(128) ;'

SELECT N'SELECT @DF_Name = d.[name] from sys.columns AS c INNER JOIN sys.default_constraints AS d
ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
WHERE c.[object_id] = object_id(''' + [Table Name] + N''') AND c.[name] = N''' + [Column Name] + N''' ;

IF @DF_Name IS NOT NULL
BEGIN
EXEC(''ALTER TABLE [dbo].[' + [Table Name] + N'] DROP CONSTRAINT ['' + @DF_Name + '']'') ;
END ;

'
FROM #DF
ORDER BY [Table Name];

--   Create the New constraints
SELECT N'IF NOT EXISTS(SELECT * FROM sys.columns AS c INNER JOIN sys.default_constraints AS d
    ON c.[object_id] = d.[parent_object_id] AND c.[column_id] = d.[parent_column_id]
WHERE c.[name] = ''' + [Column Name] + N''' AND OBJECT_NAME(d.[parent_object_id]) = ''' + [Table Name] + N''')
BEGIN
    ALTER TABLE [' + [Table Name] + N'] ADD CONSTRAINT [DF_' + [Table Name] + N'_'
            + [Column Name] + N'] DEFAULT ' + [definition] + N'FOR [' + [Column Name] + N'] ;
END ;

'
FROM #DF
ORDER BY [Table Name];

Search results matching tags 'Tips & Tricks' and 'TSQL'

sp_spaceused Alternative

Cleaning up Un-Named Defaults