Feeds:
Posts
Comments

Posts Tagged ‘sp_MSforeachtable’

In one of the my earlier articles related to triggers, I had written about how to get the list of triggers along with its different properties using sys.triggers & OBJECTPROPERTY. It was quite a big script to get these details. Recently, when I was working on some system level stored procedures, I got a shortcut to achieve list of triggers along with its schema using sp_MSforeachtable & sp_helptrigger

Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
EXEC sp_MSforeachtable
 @command1 = 'sp_helptrigger ''?'''
,@whereand = 'And Object_id In (Select parent_id From sys.triggers)';
--OUTPUT

listoftrigger.1.1

Advertisements

Read Full Post »

Today, I was working on customer’s test database and I wanted to delete all the tables having “_temp” in the names. Even though I could manually write the script by using a table at a time, I was looking for a shortcut. I have been using sp_MSforeachtable on test databases for multiple purposes. But as far as I know, it performs the specified operation in all tables in the particular database. So I started doing my research. First of all, I searched if there was any parameter available in the sp_MSforeachtable that we can use to filter tables objects and I found one parameter namely @whereand. Given below is the script to view the available parameters in sp_MSforeachtable.

EXEC sp_help sp_msforeachtable
--OUTPUT

sp_msforeachtable_conditional.1.1

SOLUTION :
You need to use the @whereand parameter to filter any table object in sp_MSforeachtable (Undocumented system stored procedure).
Given below is a script, where I will select table contains “_temp” in the name.

 USE tempdb
 GO
 EXEC sp_msforeachtable
 @command1 ='SELECT * FROM ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%_temp%'')'

Note : It is not recommended to use sp_MSforeachtable on production databases.

Read Full Post »

sp_spaceused is one of the frequently used stored procedures when it comes to view the number of rows, reserved size, data size, index size, unused space in a table. I myself utilized it many a time.

Given below are the three methods to run sp_spaceused or equivalent for all tables in a database to get number of rows, reserved size, data size, index size, unused space but this time we will measure the performance as well.

Method 1 :
Given below is one line script that can achieve the required result but it will cost you a performance issue because it will loop all the tables in the database one by one and will give you the result set.

EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]';

Given below is one of the server execution times.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 1768 ms.

If you have more tables, it will give you the error message as given below.
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

Method 2 :
Given below is another method to achieve it but this method will also cost you a performance issue because it will also loop all the tables in the database one by one and will give you the result set. Reference

Set statistics time on
CREATE TABLE ##TempTable
(name nvarchar(128)
,rows char(11)
,reserved varchar(18)
,data varchar(18)
,index_size varchar(18)
,unused varchar(18)
)
declare @UserTableName nvarchar(40)
declare UserTableSize cursor for
select rtrim(name) from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name

open UserTableSize
fetch next from UserTableSize into @UserTableName
while @@fetch_status = 0
begin
	Insert ##TempTable
	exec sp_spaceused @UserTableName
	fetch next from UserTableSize into @UserTableName
end
close UserTableSize
deallocate UserTableSize
Select *  from ##TempTable
--Drop Table ##TempTable
Set statistics time off
GO

Given below is one of the server execution times.
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 1034 ms.

Method 3 :
This method will not loop all the tables in the database one by one and also you don’t need to insert its result set into a temporary table to utilize it further. The performance of this method is also better than the other two methods also.
Given below is the script.

Set statistics time on
declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'

Select object_Name(i.object_id) as [name]
,p.rows
,Convert(varchar(50),@PageSize * SUM(total_pages)) + ' KB' as [reserved]
,Convert(varchar(50),@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) + ' KB' as [data]
,Convert(varchar(50),@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) +  ' KB' as [index_size]
,Convert(varchar(50),@PageSize * SUM(total_pages-used_pages)) +  ' KB' as [unused]
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
JOIN sys.tables t ON i.object_id=t.object_id
Where i.type<=1 and a.type=1
and
t.type='U' and is_ms_shipped=0
Group By i.object_id,p.rows
GO
Set statistics time off

Given below is the server execution times.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.

Conclusion :
You can see that all the above methods produce the same result set but the issue is with the performance. I recommend Method 3 because it will take less effort and time to generate the same result set.

Any comments ?

Read Full Post »