Find size of each table in database of SQL Server 2005/2008

Well, recently I have observed that data file of my database started growing like anything and I wonder which table(s) is causing this. Generally by looking at datafile you can’t assume that which table(s) is growing high so you need to check size of table and index. I decided to first look at the size of table, how can do that? Well there is one stored procedure in SQL Server which is really very handy in this scenario.
use adventureworks
go

–look at overall scenario about total size of table
–and index and database size etc.
EXEC sp_spaceused

–now let us look at the size of perticular table
EXEC sp_spaceused ‘Production.ProductProductPhoto’
Last T-SQL statement would give you total number of rows in table, reserved size, data size, index size and unused space. WOW, really quick and handy SP, isn’t it????
But this SP will give you information about just one table and what, if you want to look at the details about all tables in your database.
There are two ways to go for in this scenario.
1.)    Use following T-SQL which uses SP_MSForEachTable (undocumented SP), If you want to know more about this SP, click here to look at my past article.
exec sp_MSforeachtable @command1=‘print ”?” exec sp_spaceused ”?”’

2.)    Another way is really easy to go for, it’s a readymade graphical report provided by SQL Server itself. Just open up your SSMS, right click on database, Select Reports, click on Standard Reports and click on “Disk Usage by Top Tables”. There are so many other useful report too, which you can study and can use whenever you need it.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Find size of each table in database of SQL Server 2005/2008

Well, recently I have observed that data file of my database started growing like anything and I wonder which table(s) is causing this. Generally by looking at datafile you can’t assume that which table(s) is growing high so you need to check size of table and index. I decided to first look at the size of table, how can do that? Well there is one stored procedure in SQL Server which is really very handy in this scenario.
use adventureworks
go

–look at overall scenario about total size of table
–and index and database size etc.
EXEC sp_spaceused

–now let us look at the size of perticular table
EXEC sp_spaceused ‘Production.ProductProductPhoto’
Last T-SQL statement would give you total number of rows in table, reserved size, data size, index size and unused space. WOW, really quick and handy SP, isn’t it????
But this SP will give you information about just one table and what, if you want to look at the details about all tables in your database.
There are two ways to go for in this scenario.
1.)    Use following T-SQL which uses SP_MSForEachTable (undocumented SP), If you want to know more about this SP, click here to look at my past article.
exec sp_MSforeachtable @command1=‘print ”?” exec sp_spaceused ”?”’

2.)    Another way is really easy to go for, it’s a readymade graphical report provided by SQL Server itself. Just open up your SSMS, right click on database, Select Reports, click on Standard Reports and click on “Disk Usage by Top Tables”. There are so many other useful report too, which you can study and can use whenever you need it.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Table statistics in SQL Server 2005/2008/2000 like Table size, total row, index size etc.

We may often need to see total row in each table, data size, index size, used and unused space in table etc. Well, in this case SP_SpaceUsed stored procedure could be used but it shows you states of only one table, what if we wants this type of statistics for all use tables in entire database? In this case, undocumented stored procedure “SP_MsForEachTable” will come to our help. Let us see it practically.
–create SP which will display states of all tables
Create proc tableState
AS
–create temporary table
–which will store all states
CREATE TABLE #TableState (
       tableName sysname ,
       rowCounts INT,
       reservedSize VARCHAR(50),
       dataSize VARCHAR(50),
       indexSize VARCHAR(50),
       unusedSize VARCHAR(50))
SET NOCOUNT ON
–insert result of SP_SpaceUsed in temp table
–if you go for CURSOR or LOOP you don’t need to use
–sp_msforeachtable stored procedure
–but to avoid CURSOR,sp_msforeachtable could be good alternative
INSERT #TableState
  EXEC sp_msforeachtable ‘sp_spaceused ”?”’
–display data of temp table
SELECT * FROM #TableState order by tableName
–drop temp table explicitly!!
DROP TABLE #TableState
GO
–run above SP
exec tableState
 
 
Happy Programming!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Sp_MsForEachDB undocumented stored procedure in SQL Server 2005:

Sp_MsForEachDB is an un-documented stored procedure by Microsoft. It is useful when you want to perform same action in all databases in one SQL Server instance. Without Sp_MsForEachDB you have to go for cursor and you know that cursor is resource consuming and reduce the speed.

I have written article about another undocumented stored procedure named “Sp_MsForEachTable” which is for performing same action for all tables in one database. You can refer that article at:

http://ritesh-a-shah.blogspot.com/2009/03/spmsforeachtable-undocumented-but.html

http://ritesh-a-shah.blogspot.com/2009/03/empty-all-table-or-delete-all-data-from.html

Anyway, let us see how we can use Sp_MsForEachDB practically.

–this script will return all the table of all databases in sql server instance

–along with db name and schema

exec sp_MSforeachdb

‘USE ?

select ob.name,sch.name,”?” AS ”DATABASE” from ?.sys.tables ob

inner join ?.sys.schemas as sch on sch.schema_id = ob.schema_id’

This was just an example of Sp_MsForEachDB. You can use it more efficiently in your customize need like granting permission to user in every database, change some property of all databases available in instance of SQL Server etc.

Practical example of this article is created by Rashmika Vaghela (good SQL developer and my subordinate). She was inspired to write something by regularly reading my blog and asked me to contribute in any of my article. I was planning to write article about Sp_MsForEachDB so I told her to make one practical example for Sp_MsForEachDB.

Thank you Rashmika.

Reference: Ritesh Shah

Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

As a developer, many times we need to delete all the records from all tables. For example we are developing one application in our development server. At the time of developing we must have entered lots of temporary data in our database; we shouldn’t upload all those data along with structure at live server. We should have to delete all dummy and temp data.
What will you do in that case? Execute delete statement for all foreign key table first and then delete from master tables????? Or else remove all constraint, check and triggers from all tables and then execute delete statement for all tables. Uuuufffffffff so tedious and boring job!!!! Isn’t it??????

This article is moved here.

sp_MSforeachtable: Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.

sp_MSforeachtable: Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.

sp_MSforeachtable is one of the undocumented stored procedure in Microsoft SQL-Server 2005. It has an amazing facility of auto iteration. It means that it will run T-SQL command for each and every single table of your database. As other system SP, you can find this SP in MASTER DB. If you are a member of sysadmin then you can use this SP very beautifully for your task.
Let’s look at its example:
Note: Create one dummy database and use this example in it only as it will take effect throughout the database and will affect all tables resides in that database.
USE DummyDB
GO
EXEC sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO
Above query will disable all the constraints of all tables in DummyDB. “?” In above query will be replaced automatically by table name internally and will iterate the loop for all table automatically.
How easy is this??????
Reference: Ritesh Shah