SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Harsha Majety / Script to find table sizes in a database / Latest Posts

RE: Script to find table sizes in a database

kiran.konankal — Mon, 17 Jan 2011 03:16:58 GMT

can somebody tell me, how can we list all the columns from a temp(#) table without using SP_Help or system objects.

RE: Script to find table sizes in a database

Hardy21 — Tue, 14 Dec 2010 21:31:14 GMT

[quote][b]gitmo (12/14/2010)[/b][hr]In SQL 2008 I right click the database name in Object Explorer, select Reports / Standard Report / Disk Usage by Table.[/quote]Oh great... Thanks. I don't know about this - it is really useful. You can also check no of records for all the tables in a single go.

RE: Script to find table sizes in a database

m.alves.amorim — Tue, 14 Dec 2010 18:20:26 GMT

To see sizes of all tables in SQL 2005 use this:[code="sql"]DECLARE @tableName VARCHAR(1000); CREATE TABLE #AllTables ( row_num INT IDENTITY(1, 1), table_name VARCHAR(1000) );--Using temp table, i dont like to use cursorsINSERT INTO #AllTables (table_name)SELECT s.[NAME] + '.' + t.[name]FROM sys.Tables t, sys.schemas sWHERE t.[SCHEMA_ID] = s.[SCHEMA_ID]CREATE TABLE #TempTable ( tableName VARCHAR(100), [rows] VARCHAR(100), reserved VARCHAR(50), data VARCHAR(50), index_size VARCHAR(50), unused VARCHAR(50) )DECLARE @i INT;DECLARE @tableCount INT;SELECT @i = 1;SELECT @tableCount = (SELECT COUNT(1) FROM #AllTables );--Loop to get all tablesWHILE ( @i <= @tableCount ) BEGIN SELECT @tableName = table_name FROM #AllTables WHERE row_num = @i; --Dump the results of the sp_spaceused query to the temp table INSERT #TempTable EXEC sp_spaceused @tableName; SET @i = @i + 1; END;--Select all records so we can use the reultsSELECT *FROM #TempTableORDER BY data DESC;--Final cleanup!DROP TABLE #TempTableDROP TABLE #Alltables; [/code]

RE: Script to find table sizes in a database

gitmo — Tue, 14 Dec 2010 08:28:37 GMT

In SQL 2008 I right click the database name in Object Explorer, select Reports / Standard Report / Disk Usage by Table.

RE: Script to find table sizes in a database

peter-757102 — Tue, 14 Dec 2010 08:28:14 GMT

Don't forget to first use: [b]DBCC UPDATEUSAGE(0);[/b]BOL says about this:Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the [b]sp_spaceused[/b] system stored procedure.

RE: Script to find table sizes in a database

william.long — Tue, 14 Dec 2010 07:31:51 GMT

Here are some tweaks I did on my SQL Server 2005 to get it to work for me...--DECLARE @i INT = 1;DECLARE @i INT ;SET @i = 1;--DECLARE @tableCount INT = (SELECT COUNT(1) FROM #AllTables );DECLARE @tableCount INT SET @tableCount = (SELECT COUNT(1) FROM #AllTables );

RE: Script to find table sizes in a database

jordonpilling — Tue, 14 Dec 2010 05:21:43 GMT

Great Script, Just a small tweak if i may;The 'Order By' clause you use on line 45 does not really work as the data column is a nvarchar and contains " KB".replace[code="sql"]ORDER BY data DESC;[/code]with[code="sql"]ORDER BY CONVERT(INT, replace(data,' KB','')) desc;[/code]handy for databases that contain 1000+ tables

RE: Script to find table sizes in a database

Andrew Watson-478275 — Tue, 14 Dec 2010 02:27:20 GMT

You don't really avoid using cursors or loops - if you have a look inside sp_msforeachtable and sp_msforeach_worker, you'll see it uses them itself.For me, the main reason for using it is that it's easier.

RE: Script to find table sizes in a database

Hardy21 — Tue, 14 Dec 2010 02:21:05 GMT

[quote][b]Andrew Watson-478275 (12/14/2010)[/b][hr]If you don't mind using undocumented procedures, you can simplify it slightly by replacing all the loop stuff with:[code="sql"]exec sp_msforeachtable @command1='insert #TempTable exec sp_spaceused ''?'''[/code]I also convert the "KB" values from sp_spaceused into numbers to allow for easier manipulation.[/quote]Though sp_msforeachtable procedure is undocumented, but it is really useful if we want to apply the changes to all the tables in the database - by using this we can avoid Loop or cursors. We are using this to enable/disable all the constraints at one go.

RE: Script to find table sizes in a database

Andrew Watson-478275 — Tue, 14 Dec 2010 01:54:17 GMT

If you don't mind using undocumented procedures, you can simplify it slightly by replacing all the loop stuff with:[code="sql"]exec sp_msforeachtable @command1='insert #TempTable exec sp_spaceused ''?'''[/code]I also convert the "KB" values from sp_spaceused into numbers to allow for easier manipulation.

Script to find table sizes in a database

harsha.majety — Mon, 13 Dec 2010 21:57:25 GMT

Comments posted to this topic are about the item [B]Script to find table sizes in a database[/B]