Solved

How do I see details on filegroups in SQL Server 2005 beyond what is shown in db properties view?

Posted on 2009-05-07
29
1,623 Views
Last Modified: 2012-05-06
I found the following post: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23262864.html?sfQueryTermInfo=1+10+2005+filegroup+server+sql and Chapmandew's solution (see code below) is awesome...I would just like to take a step further.  Being a newbie to SQL Server though, I just can't seem to figure out how.

Ideally, I would like the tablename, its filegroup, the size of the table and the size of the indexes in the filegroup as well in a result from running the query.  Is this possible, or is there a tool that lets you do that?  I know I can look at the filegroups and see how big they are, it just doesn't show me what is in them and what accounts for what percentage of space.  This would be useful in keeping the filegroup sizes under control for maximum performance.

Thanks,
Matt
select tablename = object_name(object_id),Data_located_on_filegroup = d.name  

from sys.data_spaces d 

join sys.indexes i on d.data_space_id = i.data_space_id

where i.index_id < 2

Open in new window

0
Comment
Question by:dabdowb
  • 16
  • 7
  • 4
29 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24330776
check
sp_spaceused

http://msdn.microsoft.com/en-us/library/ms188776.aspx

--

and
Collecting SQL Server database usage information for free space and trending
http://www.mssqltips.com/tip.asp?tip=1426
0
 

Author Comment

by:dabdowb
ID: 24436211
Sorry for the delay...I have been messing with the code you sent.  Neither code has all the parts I am looking for...each one is missing an element.
I much prefer the coding on the second link, as it gives me alot of great information, but the one thing missing from it, that is the critical piece for me, is the tables in the database associated with that space being reported.  It lists the database name, the filegroup name, the space it takes, percentage of space, when it was made....all sorts of great stuff, just not the name of the table associated with the space...is there an easy way to put that in the code??
I am extremely new to SQL Server, so I am not aware of what it can/can't do, or how to do most of it.
Thanks,
matt
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24437788
please clarify: 1st script is for db level info
so do you need tables size too?
if 'yes'
it is something like this one:
http://www.mssqltips.com/tip.asp?tip=1177

0
 

Author Comment

by:dabdowb
ID: 24440824
ok, so that last link provide for the table size is terrific.  now, is there an easy way to combine the functionality of that into the programming of this link?
http://www.mssqltips.com/tip.asp?tip=1426
That would truly give me everything (and more) for what I am looking for, but it would give it to me in one output file that I could then take to my Sys Admin team to try and manage some of the elements of the database in a better way.
I noted you mentioned the 1st script you listed (http://msdn.microsoft.com/en-us/library/ms188776.aspx) gave db level info, but I wasn't sure what to plug in where, so I didn't really see much detail comparted to that of the 2nd link you gave me (http://www.mssqltips.com/tip.asp?tip=1426).  As I said, I am very new to SQL Server so I don't really understand much of the T-SQL language yet.  I know enough to get our group things they need based on some programming that was setup by an expert before he left our company, but I have ALOT to learn obviously.
The whole mission for me is to try and keep the various filegroups for my database to at or below 10GB in memory.  So as data in a filegroup approaches that level, I then create a new one and build into it for awhile, and so on.  The guy that built this database, stated that guideline before he left, BUT, he didn't follow it when he was building the database, so I am trying to go in and clean up the mess so to speak.  There are a couple filegroups that are closer to 30GB in space, but I can't seem to figure out what the heck is in them, so I want a tool that shows me all the elements of a particular database...the table names, the size of the tables, the size of the indexes associated, the filegroup they belong to and how much space in that filegroup that particular table and index are taking up, etc.
So this last link you gave me, gives me the table and index data i am looking for, now i am hoping to add on the filegroup information as well, which I noticed was in the 2nd script you sent me, so now wondering how I can somehow combine the two scripts into one file output.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24442852
file group info for tables?
can you please post prototype of result that you expect?
0
 

Author Comment

by:dabdowb
ID: 24443042
If I go into my database on SQL Server 2005 and right click its names to go to Properties, the following screen pops-up when I choose "Files" tab.  These show the filegroups and files, their size and location.  I want to see the data on this screen, but I want it broken down one more layer.  So for instance, the Logic Name and Filegroup called "a1-build" in the screenshot has 3193MB used up of the overall server space.  I want to then see what tables and indexes are being allocated to that particular Logic Name and Filegroup, with each table/index name listed, along with how much memory it is using.  So if table a1 and a1_lvl1 of my database make up the total of 3193MB, I would see each one listed showing that a1 table uses 1004MB, its one index uses 996MB, and then a1_lvl1, with no indexes of its own, takes up the remaining 1193MB of space on this particular 3193MB of memory space.
Does that work, or should I put it into an excel spreadsheet so you can see it clearer?
Just let me know.
Thanks,
Matt

Database-Properties-Window-SQL-2.doc
0
 

Author Comment

by:dabdowb
ID: 24724524
I am at a loss on this question at this point.  Though the answers provided thus far do offer some good information, it is not what I am looking for, and I don't have enough experience in SQL programming to mesh the two together somehow and produce the final snapshot.
I keep being told this question is open and I know they will close it soon if I don't chime in, so I guess this is my chime in again for help?

Thanks,
matt
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24730134
something like these - just need to wrap:

go

--use [pubs]

go

SELECT

CAST(cast(g.name as varbinary(256)) AS sysname) AS [Name],

g.data_space_id AS [ID],

g.is_default AS [IsDefault],

g.is_read_only AS [ReadOnly],

CAST(ISNULL((select sum(gs.size)*convert(float,8) from sys.database_files gs where gs.data_space_id = g.data_space_id), 0) AS float) AS [Size]

FROM

sys.filegroups AS g

ORDER BY

[Name] ASC

go
 
 

			create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))

			insert #tmpspc EXEC ('dbcc showfilestats')

		
 
 

SELECT

CAST(cast(g.name as varbinary(256)) AS sysname) AS [FileGroup_Name],

s.name AS [Name],

(tspc.TotalExtents - tspc.UsedExtents)*convert(float,64) AS [AvailableSpace],

s.physical_name AS [FileName],

CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],

CAST(CASE when s.growth=0 THEN 99 ELSE s.is_percent_growth END AS int) AS [GrowthType],

s.file_id AS [ID],

CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],

CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],

s.size * CONVERT(float,8) AS [Size],

CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],

CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],

s.is_read_only AS [IsReadOnly],

s.is_media_read_only AS [IsReadOnlyMedia],

s.is_sparse AS [IsSparse]

FROM

sys.filegroups AS g

INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)

INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id

ORDER BY

[FileGroup_Name] ASC,[Name] ASC
 

			drop table #tmpspc

		
 

go
 
 

			create table #tmplogspc (DatabaseName sysname, LogSize float, SpaceUsedPerc float, Status bit)

			insert #tmplogspc EXEC ('dbcc sqlperf(logspace)')

		
 
 

SELECT

s.name AS [Name],

s.physical_name AS [FileName],

CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],

CAST(CASE when s.growth=0 THEN 99 ELSE s.is_percent_growth END AS int) AS [GrowthType],

s.file_id AS [ID],

CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],

s.size * CONVERT(float,8) AS [Size],

tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24 AS [UsedSpace],

CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],

s.is_read_only AS [IsReadOnly],

s.is_media_read_only AS [IsReadOnlyMedia],

s.is_sparse AS [IsSparse]

FROM

sys.master_files AS s

INNER JOIN #tmplogspc tspclog ON 

			tspclog.DatabaseName = db_name()

		

WHERE

(s.type = 1 and s.database_id = db_id())

ORDER BY

[Name] ASC
 

			drop table #tmplogspc

		

Open in new window

0
 

Author Comment

by:dabdowb
ID: 24736943
OK, so that second SELECT-CAST group is awesome!  Love that one alot and will use it for certain for sys admin work.  The data shown from that select statement series is all about the filegroups and its extended attributes, which is terrific....it is the one step further part that seems to be the hard part...and maybe what I am asking for isn't even possible in one query.  In fact, if this part I am about to explain again needs to be in a seperate query, I am good with that and can just run the two jobs seperately.  This one first to show which filegroups need attention, and the second one, the one I am about to explain again, for WHERE to attack the administration work.
In the query results from the SELECT-CAST statement provided, one line specified the filegroup "a1_build".  It shows that 6,601,600 Kb of memory are being used in that filegroup.  What I want to know via a query, is what tables and indexes in the database are being attributed to that filegroup.  I can find that out by opening every table and every index in the database and looking at the properties to see what filegroup it is attributed to, but that is very time consuming and completely silly.  I would prefer to just kick off a query that would show me something like this:
Filegroup   Tablename/Index   MemoryUsedByTable/Index    TotalMemoryInFilegroup  
a1_build     a1_lvl1                      500,000                                      6,601,600
a1_build     a2_lvl1                      1,500,000                                   6,601,600
a1_build     a_master                  3,100,000                                   6,601,600
a1_build     item_loc                    900,000                                      6,601,600
a1_build     a3                               601,600                                      6,601,600
Then if there was another filegroup called "demand" with 2,000,000 Kb, I would want to see the detail listed on that filegroup using the same format, all in the same output table.

Filegroup   Tablename/Index   MemoryUsedByTable/Index    TotalMemoryInFilegroup  
a1_build     a1_lvl1                      500,000                                      6,601,600
a1_build     a2_lvl1                      1,500,000                                   6,601,600
a1_build     a_master                  3,100,000                                   6,601,600
a1_build     item_loc                    900,000                                      6,601,600
a1_build     a3                               601,600                                      6,601,600
demand      history                       1,350,000                                   2,000,000
demand      fcst_id                        650,000                                      2,000,000
 Additional detail that administrators out there think is useful, by all means put it in.  I have no issue with excess data, just hoping to get this core portion of data for quick database management as filegroups grow to large.
Does this make sense?

Thanks,
Matt
0
 

Author Comment

by:dabdowb
ID: 24915811
just checking in to see if there is any chatter on this one....it appears the experts may be a bit stumped on this one??
Cheers,
matt
0
 

Author Comment

by:dabdowb
ID: 25126372
OK, going to give this one more week, and then I guess I have no choice but to close the ticket as unanswered.  While there is some good stuff here, it is not what I asked for, so I don't see how I can award points, unless someone else has a suggestion.
I am open to ideas.

Thanks,
matt
0
 

Author Comment

by:dabdowb
ID: 25259549
Hello Moderators....I would LOVE to close this question so I stop getting your reminders, but I don't have a complete answer yet.  There is some good information in here relating to what I asked, but not the correct solution I am looking for, so how to end the question?
I think some credit should be given to those who worked on it, even though it isn't exactly what I wanted.  To close it without giving credit to them for their work seems plain wrong.  But I don't want to say an answer is right and mislead future readers either.
Help?

Thanks,
Matt
0
 

Author Comment

by:dabdowb
ID: 25259560
I would REALLY LOVE to close the question with the right answer...that would just make my month I tell ya.
 
 
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dabdowb
ID: 25322393
Thanks Vee_Mod...I really appreciate all the help!!

Cheers,
Matt
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25322476
I haven't been able to see all the code so far, but will check it tomorrow.

But it sounds like you're talking about about how Oracle handles tables and tablespaces. I'm not sure if that even exists in SQL.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25326459
I was wrong -- this can be done. Learning something new.

I think the issue is that you can't get there from here in one step. Looking at the stuff that shows the table sizes, and the stuff that shows the database info, both are using some sort of temp table that doesn't retain the data afterward.

The one to many of the databases to the tables would give you a very redundant report depending on the tables and design of your DB(s).

Also it doesn't give you a true trending other than manually copying the data to somewhere else.

It would be relatively easy to turn these around to store the data in permanent tables in this (or another) server. Then you can make them a stored procedure and run them on a regular schedule from the SQL Agent.  After the data is stored, you can and then use some reporting tool such as Crystal, Access or whatever to link the DB header overall and reporting on the structure underneath. That is instead of going to mildly Cartesian/de-normalized table design.


--create permanent table

CREATE TABLE ReportingDB.dbo.TableSizing(    

        tablename sysname

,       row_count INT

,       reserved VARCHAR(50) collate database_default

,       data VARCHAR(50) collate database_default

,       index_size VARCHAR(50) collate database_default

,       unused VARCHAR(50) collate database_default 

,       Database_Name VARCHAR(50)

,       DateCollected DateTime Default GetDate()

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

-- create the custom stored proc to get the data

-- and put it in the perm table. Then schedule a

-- SQL agent job to run this as an sp_MSforeachdb 

-- on the server. Convert the other script and just

-- schedule it for a regular run.

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

CREATE CP_TableSizing

AS

BEGIN try 

DECLARE @table_name VARCHAR(500) ; 

DECLARE @schema_name VARCHAR(500) ; 

DECLARE @tab1 TABLE(

        tablename VARCHAR (500) collate database_default

,       schemaname VARCHAR(500) collate database_default

); 
 

INSERT INTO @tab1 

SELECT t1.name

,       t2.name 

FROM sys.tables t1 

INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
 

DECLARE c1 CURSOR FOR 

SELECT t2.name + '.' + t1.name  

FROM sys.tables t1 

INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
 

OPEN c1; 

FETCH NEXT FROM c1 INTO @table_name;

WHILE @@FETCH_STATUS = 0 

BEGIN  

        SET @table_name = REPLACE(@table_name, '[',''); 

        SET @table_name = REPLACE(@table_name, ']',''); 
 

        -- make sure the object exists before calling sp_spacedused

        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))

        BEGIN

                INSERT INTO ReportingDB.dbo.TableSizing EXEC sp_spaceused @table_name, false, db_name,

					 cast(convert(varchar,getdate(),101) as datetime) ;

        END

        

        FETCH NEXT FROM c1 INTO @table_name; 

END; 

CLOSE c1; 

DEALLOCATE c1; 

SELECT t1.*

,       t2.schemaname 

FROM @temp_table t1 

INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )

ORDER BY  schemaname,tablename;

END try 

BEGIN catch 

SELECT -100 AS l1

,       ERROR_NUMBER() AS tablename

,       ERROR_SEVERITY() AS row_count

,       ERROR_STATE() AS reserved

,       ERROR_MESSAGE() AS data

,       1 AS index_size, 1 AS unused, 1 AS schemaname 

END catch

Open in new window

0
 

Author Comment

by:dabdowb
ID: 25327078
I really like the idea of a stored procedure to see trending jimpen...brilliant!  Unfortunately, I am very new to SQL Server, so when I tried to copy this and paste it into a new query on my database in SQL Server 2005, it gave the following errors.  I looked through, but wasn't sure what it refers too...I have noticed SQL's error coding is far from descriptive....at least it points out line numbers...a step of from Access that just says NO, but doesn't help when you are a novice like myself.

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'CREATE'.
Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'convert'.
Msg 1087, Level 15, State 2, Line 59
Must declare the table variable "@temp_table".
 
I know before someone asked for a prototype, so I made one manually...took forever, and was outdated the next day, which is why I am looking for something I can run live and dump to a table or external DB.  Below is the attachment of my efforts.  While repetitive on some lines, it gives the basic idea.  From what I can see on your code, it looks like it would grab most of this, except maybe the filegroup name was missing, which would be great for knowing what tables are in what filegroup, when the filegroup gets to big or needs to be moved....kind of the point behind the whole thing really.  I am being asked to keep each filegroup under 10GB in size, so when the filegroup gets bigger than that, I want to know what tables are in that filegroup so I can then attempt to move them eslewhere, shrink the db or even delete if needed.  A script/query would be ALOT better than looking at the properties of every table and index on the server as you may have guessed.
Thanks,
Matt

SQL-Mapping.xls
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25327690
Took me a while to dig this out. Can you test the query on yours as I don't have any DBs with filegroups.
SELECT t1.name 

,       t2.name ,  FG.FileGroupNm, FG.TblType

FROM sys.tables t1 

INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id ) 

LEFT JOIN (SELECT o.[name] AS TblName, o.[type] as TblType,  f.[name] As FileGroupNm

	FROM sys.indexes i

	INNER JOIN sys.filegroups f

	ON i.data_space_id = f.data_space_id

	INNER JOIN sys.all_objects o

	ON i.[object_id] = o.[object_id]

	--WHERE i.data_space_id = 2 --* New FileGroup*

	GROUP BY o.[name], o.[type],  f.[name]) FG 

	ON (t1.name = FG.TblName)

Open in new window

0
 

Author Comment

by:dabdowb
ID: 25327978
Hi jimpen,
So that worked for showing me the table names and what filegroup they were in.  The name field just showed 'dbo' in the output, so not certain what it is supposed to show.  As well, TblType showed 'U' for all of the tables...again, not sure what it is supposed to show or what that means...could be completely right, just letting you know.
So is the idea that these jobs would be run seperately, one to get the size and then one to get this breakdown, or were you testing to make sure this part worked first before adding the size of the tables/indexes into the results window?
Thanks again,
Matt
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25334187
That is part of the larger procedure I'm working on. But I'm tied up in my day job for a moment. Don't abandon hope yet. I can find it useful for myself as well. I just need to get it in the stack.
0
 

Author Comment

by:dabdowb
ID: 25334971
No worries jimpen.  I am a very patient person and am just grateful for all your help above all else.  I wish I wasn't so SQL challenged so I could help...but I keep learning a bit more each day.

Cheers,
Matt
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 25335453
Here's what I have so far.

It is pretty much down to a truncating data on the insert statements. I thought 500 characters for object name is excessive. And it is designed to truncate the time off the insert into the Master.dbo.TableSizing. So if you want to run it multiple times a day, you would have to decide how you would want to handle that.
USE [master]

GO

/****** Object:  Table [dbo].[TableSizing]    Script Date: 09/14/2009 14:10:44 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableSizing]') AND type in (N'U'))

BEGIN

	DROP TABLE [dbo].[TableSizing]

END
 

--create permanent table

CREATE TABLE Master.dbo.TableSizing(    

        tablename VARCHAR(100)

,       row_count INT

,       reserved VARCHAR(50) collate database_default

,       data VARCHAR(50) collate database_default

,       index_size VARCHAR(50) collate database_default

,       unused VARCHAR(50) collate database_default 

,		FileGroupNm  VARCHAR(50)

,		TblType VARCHAR(5)

,       Database_Name VARCHAR(50)

,       DateCollected DateTime Default GetDate())

GO

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

-- create the custom stored proc to get the data

-- and put it in the perm table. Then schedule a

-- SQL agent job to run this as an sp_MSforeachdb 

-- on the server. Convert the other script and just

-- schedule it for a regular run.

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

GO

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[CP_TableSizing]    Script Date: 09/14/2009 14:15:44 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CP_TableSizing]') AND type in (N'P', N'PC'))

BEGIN

	DROP PROCEDURE [dbo].[CP_TableSizing]

END

GO

use adventureworks

go
 

--CREATE PROCEDURE CP_TableSizing

-- AS

BEGIN try 

DECLARE @table_name VARCHAR(100) ; 

DECLARE @schema_name VARCHAR(100) ; 

DECLARE @tab1 TABLE(

        tablename VARCHAR (100) collate database_default

,       schemaname VARCHAR(100) collate database_default

,       FileGroupNm	VARCHAR(50) collate database_default

,		TblType VARCHAR(50) collate database_default); 
 

DECLARE  @temp_table TABLE (    

        tablename sysname

,       row_count INT

,       reserved VARCHAR(50) collate database_default

,       data VARCHAR(50) collate database_default

,       index_size VARCHAR(50) collate database_default

,       unused VARCHAR(50) collate database_default 

); 
 

INSERT INTO @tab1 

SELECT t1.name 

,       t2.name ,  FG.FileGroupNm, FG.TblType

FROM sys.tables t1 

INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id ) 

LEFT JOIN (SELECT o.[name] AS TblName, o.[type] as TblType,  f.[name] As FileGroupNm

		FROM sys.indexes i

		INNER JOIN sys.filegroups f

		ON i.data_space_id = f.data_space_id

		INNER JOIN sys.all_objects o

		ON i.[object_id] = o.[object_id]

		GROUP BY o.[name], o.[type],  f.[name]) FG 

	ON (t1.name = FG.TblName)

DECLARE c1 CURSOR FOR 

SELECT t2.name + '.' + t1.name  

FROM sys.tables t1 

INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   
 

OPEN c1; 

FETCH NEXT FROM c1 INTO @table_name;

WHILE @@FETCH_STATUS = 0 

BEGIN  

        SET @table_name = REPLACE(@table_name, '[',''); 

        SET @table_name = REPLACE(@table_name, ']',''); 
 

        -- make sure the object exists before calling sp_spacedused

        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))

        BEGIN

                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false;

        END

        

        FETCH NEXT FROM c1 INTO @table_name; 

END; 

CLOSE c1; 

DEALLOCATE c1; 
 

SELECT t1.*

	, t2.schemaname , t2.FileGroupNm , db_name(),

	cast(convert(varchar,getdate(),101) as datetime)

FROM @temp_table t1 

INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )

ORDER BY  schemaname,tablename;
 
 
 

INSERT INTO Master.dbo.TableSizing

SELECT t1.*

	, t2.schemaname , t2.FileGroupNm , db_name(),

	cast(convert(varchar,getdate(),101) as datetime)

FROM @temp_table t1 

INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )

ORDER BY  schemaname,tablename;

END try 

BEGIN catch 

SELECT -100 AS l1

,       ERROR_NUMBER() AS tablename

,       ERROR_SEVERITY() AS row_count

,       ERROR_STATE() AS reserved

,       ERROR_MESSAGE() AS data

,       1 AS index_size, 1 AS unused, 1 AS schemaname 

END catch
 

GO

use adventureworks

go
 

-- exec Master.dbo.CP_TableSizing;
 

go

select *

FROM Master.dbo.TableSizing

GO
 

--select db_name()

Open in new window

0
 

Author Comment

by:dabdowb
ID: 25336489
jimpen, this is pure genius!!  That is exactly what I was hoping to get in the results from this query.  AWESOME!!  Thanks you so much for your time and effort, and it is great to hear that you believe you have use for this type of data as well.  Absolute bonus.
Have a great day and thanks again!!
Cheers,
Matt
0
 

Author Closing Comment

by:dabdowb
ID: 31579160
A HUGE timesaver for my administration needs.  Hats off jimpen!!
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25336993
Glad to be of assistance. May all your days get brighter and brighter.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25340431
Just to give credit to where its due. The base part of the SP is derived from the work at mssqltips.

Determining space used for each table in a SQL Server database
Written By: Greg Robidoux -- 2/12/2007
http://www.mssqltips.com/tip.asp?tip=1177

You may want to modify the code and put that in as a comment (wrap it with a /** and after with **/ ) to indicate its a comment. And maybe add a link to this Q too. That way anyone who comes after you can get back to the source instead of just saying "WTF?".
0
 

Author Comment

by:dabdowb
ID: 25340833
great idea jimpen.  I have updated the code accordingly.  Thanks!  Documentation goes a LONG way I have found, so thanks for letting me know.
 
Cheers,
Matt
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now