Help with SP desing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with SP desing

Author

Topic

nergal
Starting Member

2 Posts

Posted - 2009-11-12 : 05:25:50

Hello,

I'm using the following SP to extract some info from about 350 tables with the same name only changing the last 2 digit (ex. tablename01, tablename02)

With this SP i have to run it manualy in all the tables, but i would like to know if it's possible to run it automatically in all of them giving me the result's all together.

Hope you can help me.

Thx

Here is the SP

USE [_DBname_]
GO
/****** Object: StoredProcedure [dbo].[ProcedureCacheUsage] Script Date: 11/12/2009 10:19:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[ProcedureCacheUsage]
(
@Table sysname,
@Owner sysname = NULL,
@LoginName nvarchar(256) = NULL,
@HostName nvarchar(256) = NULL,
@ApplicationName nvarchar(256) = NULL,
@NTUserName nvarchar(256) = NULL,
@StartTime datetime = NULL,
@EndTime datetime = NULL,
@Debug bit = 0
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @SELECT nvarchar(25), @ColumnList nvarchar(650), @FROM nvarchar(150), @WHERE nvarchar(250), @ORDERBY nvarchar(250)

SET @SELECT = 'SELECT '
SET @ColumnList = '
COALESCE(CAST(DATEDIFF(mi, MIN(StartTime), MAX(StartTime))/60. AS decimal(5, 2)), 0) AS ''Trace Duration (Hours)'',
COALESCE(SUM(CASE WHEN EventClass = 42 THEN 1 ELSE 0 END), 0) AS ''Total Stored Procedures started'',
COALESCE(SUM(CASE WHEN EventClass = 37 THEN 1 ELSE 0 END), 0) AS ''Total Stored Procedure Recompiled'',
COALESCE(SUM(CASE WHEN EventClass = 34 THEN 1 ELSE 0 END), 0) AS ''Total Cache Misses'',
COALESCE(SUM(CASE WHEN EventClass = 38 THEN 1 ELSE 0 END), 0) AS ''Total Cache Hits'',
COALESCE(SUM(CASE WHEN EventClass = 39 THEN 1 ELSE 0 END), 0) AS ''Total Execution Context Hits'''

SET @FROM = ' FROM ' + QUOTENAME(COALESCE(@Owner, 'dbo')) + '.' + QUOTENAME(@Table)

SET @WHERE = ' WHERE EventClass IN (34, 37, 38, 39, 42)'

IF @LoginName IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND LoginName LIKE ' + QUOTENAME(@LoginName, '''')
END

IF @HostName IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND HostName LIKE ' + QUOTENAME(@HostName, '''')
END

IF @ApplicationName IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND ApplicationName LIKE ' + QUOTENAME(@ApplicationName, '''')
END

IF @NTUserName IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND NTUserName LIKE ' + QUOTENAME(@NTUserName, '''')
END

IF (@StartTime IS NOT NULL) AND (@EndTime IS NOT NULL)
BEGIN
SET @WHERE = @WHERE + ' AND StartTime BETWEEN ' + QUOTENAME(CONVERT(varchar, @StartTime, 109), '''') + ' AND ' + QUOTENAME(CONVERT(varchar, @EndTime, 109), '''')
END
ELSE IF @StartTime IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND StartTime >= ' + QUOTENAME(CONVERT(varchar, @StartTime, 109), '''')
END
ELSE IF @EndTime IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND StartTime <= ' + QUOTENAME(CONVERT(varchar, @EndTime, 109), '''')
END

SET @ORDERBY = ''

IF @Debug = 1
BEGIN
SELECT @SELECT + char(13) + @ColumnList + char(13) + @FROM + char(13) + @WHERE + char(13) + @ORDERBY
END

EXEC(@SELECT + @ColumnList + @FROM + @WHERE + @ORDERBY)

END

nergal
Starting Member

2 Posts

Posted - 2009-11-13 : 04:47:12

can anyone help me plz?

i tried to use a if with goto, but the only thing i could do was repeat it several times, and what i want is all the result's in the same query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources