How to retrieve size information for all the tables in a SQL Server Database

In an older post, I described how you could rebuild all the indexes of a database in SQL Server by making use of the undocumented stored procedure "sp_MSforeachtable".

Another common task is when you want to retrieve size information for all the tables in a database.

Again, by using "sp_MSforeachtable", you can easily do that in three simple steps:

--Step 1:
--Create temporaty table for the session
create table #tblInfo(
[name] nvarchar (255),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100)
);


--Step 2:
--Using the stored procedure sp_spaceused retrieve
--the size information for all tables and store it in the temporary table
EXEC sp_MSforeachtable @command1="INSERT #tblInfo EXEC sp_spaceused '?'";


--Step 3:
--Access the results
select * from #tblInfo;

Note: Because sp_spaceused returns the size information as a string (except the number of rows), you will have to manipulate the data in the temporary table using casting prior to run sorting operations etc.

Examples:

--Sort the results by unused space (descending)
select * from #tblInfo
order by cast(substring(unused,0,charindex(' ',unused)) as int) desc;


--Sort the results by reserved space (descending)
select * from #tblInfo
order by cast(substring(reserved,0,charindex(' ',reserved)) as int) desc;

Additionally, you can create another temporary table which can contain the converted values (i.e. in MB instead of KB) of the first table.

For examnple:

select [name],
rows,
cast(substring(reserved,0,charindex(' ',reserved)) as int)/1024 as reserved_in_MB,
cast(substring(data,0,charindex(' ',data)) as int)/1024 as data_in_MB,
cast(substring(index_size,0,charindex(' ',index_size)) as int)/1024 as index_size_in_MB,
cast(substring(unused,0,charindex(' ',unused)) as int)/1024 as unused_in_MB
into #tblInfoConverted
from #tblInfo;


--and ... voila!
select *
from #tblInfoConverted
order by reserved_in_MB desc;


Until next time!
Read more on this article...

Listing Directory Contents using T-SQL

In many cases during the development of a sophisticated SQL Server Agent job, you might need to access the file system. For example you might want to list the contents of a specific directory and based on the output to proceed to other actions folllowing a series of steps that belong to a broader logic implemented via a SQL Server Agent job.

Don't worry, there's nothing stopping you from performing the above operation!

For such and similar purposes you can always use the extended stored procedure xp_cmdshell.

As described in SQL Server BoL, xp_cmdshell takes as a parameter a string and passes it to a Windows command shell for execution. Any output is returned as rows of text.

xp_cmdshell, by default, is disabled within a SQL Server instance and there is a good reason for this. When this extended stored procedure is enabled, it introduces a security risk that you should be aware of. As xp_cmdshell allows executing commands on the Operating System's level, you should be extremely careful with who has the right to execute this stored procedure. To this end, you should always use xp_cmd with caution.

OK, enough with the theory. Let's proceed with a practical example and some T-SQL!

--You can enable xp_cmdshell using the following T-SQL statement:
exec sp_configure 'xp_cmdshell',1
GO
RECONFIGURE WITH override
GO


Then, if you want for example to list the contents of the directory "c:\tmp" you can do so by executing the following T-SQL statement:

-- You first create a temporary table for storing the contents of the directory.
CREATE TABLE #dirContents
(
contents NVARCHAR(255)
)
GO


-- You then execute xp_cmdshell by using the DOS "dir" command.
-- You store the output to the temporary table created earlier.
-- The contents are stored as string expressions line-by-line.
-- Each empty line is a NULL.
INSERT INTO #dirContents
EXEC xp_cmdshell 'dir "c:\tmp"';


-- You can then scan the table for the output and analyze the data
-- using string manipulation techniques
SELECT *
FROM #dirContents

If you want to disable xp_cmdshell, you can do so as follows:

exec sp_configure 'xp_cmdshell',0
GO
RECONFIGURE WITH override
GO


I hope you found the post useful!

Cheers!
Read more on this article...