Nedd help to run this script on all DBs
in [Programming SQL]
|
From: nzrdb6 on 7 May 2010 11:57 I'm struggling to get this script to run on all DBs. It runs on a single DB just fine. Tried sp_msforeachdb but can't get the syntax quite right when using it in conjunction with @sql. Any ideas please. Thanks very much - SET QUOTED_IDENTIFIER OFF DECLARE @sql VARCHAR(4000) SET @sql = " set nocount on create table #TABLE_SPACE_WORK ( TABLE_NAME sysname not null , TABLE_ROWS numeric(18,0) not null , RESERVED varchar(50) not null , DATA varchar(50) not null , INDEX_SIZE varchar(50) not null , UNUSED varchar(50) not null , ) create table #TABLE_SPACE_USED ( Seq int not null identity(1,1) primary key clustered, TABLE_NAME sysname not null , TABLE_ROWS numeric(18,0) not null , RESERVED varchar(50) not null , DATA varchar(50) not null , INDEX_SIZE varchar(50) not null , UNUSED varchar(50) not null , ) create table #TABLE_SPACE ( Seq int not null identity(1,1) primary key clustered, TABLE_NAME SYSNAME not null , TABLE_ROWS int not null , RESERVED int not null , DATA int not null , INDEX_SIZE int not null , UNUSED float not null , USED_MB numeric(18,4) not null, USED_GB numeric(18,4) not null, AVERAGE_BYTES_PER_ROW numeric(18,5) null, AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null, AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null, AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null, ) declare @fetch_status int declare @proc varchar(200) select @proc = rtrim(db_name())+'.dbo.sp_spaceused' declare Cur_Cursor cursor local for select TABLE_NAME = rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' order by 1 open Cur_Cursor declare @TABLE_NAME varchar(200) select @fetch_status = 0 while @fetch_status = 0 begin fetch next from Cur_Cursor into @TABLE_NAME select @fetch_status = @@fetch_status if @fetch_status <> 0 begin continue end truncate table #TABLE_SPACE_WORK insert into #TABLE_SPACE_WORK ( TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED ) exec @proc @objname = @TABLE_NAME update #TABLE_SPACE_WORK set TABLE_NAME = @TABLE_NAME insert into #TABLE_SPACE_USED ( TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED ) select TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED from #TABLE_SPACE_WORK end --While end close Cur_Cursor deallocate Cur_Cursor insert into #TABLE_SPACE ( TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED, USED_MB, USED_GB, AVERAGE_BYTES_PER_ROW, AVERAGE_DATA_BYTES_PER_ROW, AVERAGE_INDEX_BYTES_PER_ROW, AVERAGE_UNUSED_BYTES_PER_ROW ) select TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED, USED_MB = round(convert(numeric(25,10),RESERVED)/ convert(numeric(25,10),1024),4), USED_GB = round(convert(numeric(25,10),RESERVED)/ convert(numeric(25,10),1024*1024),4), AVERAGE_BYTES_PER_ROW = case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),RESERVED))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_DATA_BYTES_PER_ROW = case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),DATA))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_INDEX_BYTES_PER_ROW = case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),INDEX_SIZE))/ convert(numeric(25,10),TABLE_ROWS),5) else null end, AVERAGE_UNUSED_BYTES_PER_ROW = case when TABLE_ROWS <> 0 then round( (1024.000000*convert(numeric(25,10),UNUSED))/ convert(numeric(25,10),TABLE_ROWS),5) else null end from ( select TABLE_NAME, TABLE_ROWS, RESERVED = convert(int,rtrim(replace(RESERVED,'KB',''))), DATA = convert(int,rtrim(replace(DATA,'KB',''))), INDEX_SIZE = convert(int,rtrim(replace(INDEX_SIZE,'KB',''))), UNUSED = convert(int,rtrim(replace(UNUSED,'KB',''))) from #TABLE_SPACE_USED aa ) a order by TABLE_NAME select GETDATE() TIME,DB_NAME() DB,* from #TABLE_SPACE order by 7 desc " EXEC ( @sql )
From: Sylvain Lafontaine on 7 May 2010 15:25 Did you try to put all this into a stored procedure and calling this stored procedure with sp_msforeachdb? -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "nzrdb6" <alex.campbell(a)sqltechconsulting.co.uk> wrote in message news:b5640335-5a6b-4740-bd5e-1a10c92a93ab(a)24g2000yqy.googlegroups.com... > I'm struggling to get this script to run on all DBs. It runs on a > single DB just fine. Tried sp_msforeachdb but can't get the syntax > quite right when using it in conjunction with @sql. Any ideas please. > Thanks very much - > > SET QUOTED_IDENTIFIER OFF > DECLARE @sql VARCHAR(4000) > SET @sql = " > set nocount on > > > create table #TABLE_SPACE_WORK > ( > TABLE_NAME sysname not null , > TABLE_ROWS numeric(18,0) not null , > RESERVED varchar(50) not null , > DATA varchar(50) not null , > INDEX_SIZE varchar(50) not null , > UNUSED varchar(50) not null , > ) > create table #TABLE_SPACE_USED > ( > Seq int not null > identity(1,1) primary key clustered, > TABLE_NAME sysname not null , > TABLE_ROWS numeric(18,0) not null , > RESERVED varchar(50) not null , > DATA varchar(50) not null , > INDEX_SIZE varchar(50) not null , > UNUSED varchar(50) not null , > ) > create table #TABLE_SPACE > ( > Seq int not null > identity(1,1) primary key clustered, > TABLE_NAME SYSNAME not null , > TABLE_ROWS int not null , > RESERVED int not null , > DATA int not null , > INDEX_SIZE int not null , > UNUSED float not null , > USED_MB numeric(18,4) not null, > USED_GB numeric(18,4) not null, > AVERAGE_BYTES_PER_ROW numeric(18,5) null, > AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null, > AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null, > AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null, > ) > declare @fetch_status int > declare @proc varchar(200) > select @proc = rtrim(db_name())+'.dbo.sp_spaceused' > declare Cur_Cursor cursor local > for > select > TABLE_NAME = > rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME) > from > INFORMATION_SCHEMA.TABLES > where > TABLE_TYPE = 'BASE TABLE' > order by > 1 > open Cur_Cursor > declare @TABLE_NAME varchar(200) > select @fetch_status = 0 > while @fetch_status = 0 > begin > fetch next from Cur_Cursor > into > @TABLE_NAME > select @fetch_status = @@fetch_status > if @fetch_status <> 0 > begin > continue > end > truncate table #TABLE_SPACE_WORK > insert into #TABLE_SPACE_WORK > ( > TABLE_NAME, > TABLE_ROWS, > RESERVED, > DATA, > INDEX_SIZE, > UNUSED > ) > exec @proc @objname = > @TABLE_NAME > update #TABLE_SPACE_WORK > set > TABLE_NAME = @TABLE_NAME > insert into #TABLE_SPACE_USED > ( > TABLE_NAME, > TABLE_ROWS, > RESERVED, > DATA, > INDEX_SIZE, > UNUSED > ) > select > TABLE_NAME, > TABLE_ROWS, > RESERVED, > DATA, > INDEX_SIZE, > UNUSED > from > #TABLE_SPACE_WORK > end --While end > close Cur_Cursor > deallocate Cur_Cursor > insert into #TABLE_SPACE > ( > TABLE_NAME, > TABLE_ROWS, > RESERVED, > DATA, > INDEX_SIZE, > UNUSED, > USED_MB, > USED_GB, > AVERAGE_BYTES_PER_ROW, > AVERAGE_DATA_BYTES_PER_ROW, > AVERAGE_INDEX_BYTES_PER_ROW, > AVERAGE_UNUSED_BYTES_PER_ROW > ) > select > TABLE_NAME, > TABLE_ROWS, > RESERVED, > DATA, > INDEX_SIZE, > UNUSED, > USED_MB = > round(convert(numeric(25,10),RESERVED)/ > convert(numeric(25,10),1024),4), > USED_GB = > round(convert(numeric(25,10),RESERVED)/ > convert(numeric(25,10),1024*1024),4), > AVERAGE_BYTES_PER_ROW = > case > when TABLE_ROWS <> 0 > then round( > (1024.000000*convert(numeric(25,10),RESERVED))/ > convert(numeric(25,10),TABLE_ROWS),5) > else null > end, > AVERAGE_DATA_BYTES_PER_ROW = > case > when TABLE_ROWS <> 0 > then round( > (1024.000000*convert(numeric(25,10),DATA))/ > convert(numeric(25,10),TABLE_ROWS),5) > else null > end, > AVERAGE_INDEX_BYTES_PER_ROW = > case > when TABLE_ROWS <> 0 > then round( > (1024.000000*convert(numeric(25,10),INDEX_SIZE))/ > convert(numeric(25,10),TABLE_ROWS),5) > else null > end, > AVERAGE_UNUSED_BYTES_PER_ROW = > case > when TABLE_ROWS <> 0 > then round( > (1024.000000*convert(numeric(25,10),UNUSED))/ > convert(numeric(25,10),TABLE_ROWS),5) > else null > end > from > ( > select > TABLE_NAME, > TABLE_ROWS, > RESERVED = > convert(int,rtrim(replace(RESERVED,'KB',''))), > DATA = > convert(int,rtrim(replace(DATA,'KB',''))), > INDEX_SIZE = > convert(int,rtrim(replace(INDEX_SIZE,'KB',''))), > UNUSED = > convert(int,rtrim(replace(UNUSED,'KB',''))) > from > #TABLE_SPACE_USED aa > ) a > order by > TABLE_NAME > select GETDATE() TIME,DB_NAME() DB,* from #TABLE_SPACE order by 7 desc > > > " > > EXEC ( @sql > )
From: nzrdb6 on 7 May 2010 15:45 On May 7, 8:25 pm, "Sylvain Lafontaine" <sylvainlafontaine2...(a)yahoo.ca> wrote: > Did you try to put all this into a stored procedure and calling this stored > procedure with sp_msforeachdb? > > -- > Sylvain Lafontaine, ing. > MVP - Windows Live Platform > Blog/web site:http://coding-paparazzi.sylvainlafontaine.com > Independent consultant and remote programming for Access and SQL-Server > (French) > > "nzrdb6" <alex.campb...(a)sqltechconsulting.co.uk> wrote in message > > news:b5640335-5a6b-4740-bd5e-1a10c92a93ab(a)24g2000yqy.googlegroups.com... > > > > > I'm struggling to get this script to run on all DBs. It runs on a > > single DB just fine. Tried sp_msforeachdb but can't get the syntax > > quite right when using it in conjunction with @sql. Any ideas please. > > Thanks very much - > > > SET QUOTED_IDENTIFIER OFF > > DECLARE @sql VARCHAR(4000) > > SET @sql = " > > set nocount on > > > create table #TABLE_SPACE_WORK > > ( > > TABLE_NAME sysname not null , > > TABLE_ROWS numeric(18,0) not null , > > RESERVED varchar(50) not null , > > DATA varchar(50) not null , > > INDEX_SIZE varchar(50) not null , > > UNUSED varchar(50) not null , > > ) > > create table #TABLE_SPACE_USED > > ( > > Seq int not null > > identity(1,1) primary key clustered, > > TABLE_NAME sysname not null , > > TABLE_ROWS numeric(18,0) not null , > > RESERVED varchar(50) not null , > > DATA varchar(50) not null , > > INDEX_SIZE varchar(50) not null , > > UNUSED varchar(50) not null , > > ) > > create table #TABLE_SPACE > > ( > > Seq int not null > > identity(1,1) primary key clustered, > > TABLE_NAME SYSNAME not null , > > TABLE_ROWS int not null , > > RESERVED int not null , > > DATA int not null , > > INDEX_SIZE int not null , > > UNUSED float not null , > > USED_MB numeric(18,4) not null, > > USED_GB numeric(18,4) not null, > > AVERAGE_BYTES_PER_ROW numeric(18,5) null, > > AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null, > > AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null, > > AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null, > > ) > > declare @fetch_status int > > declare @proc varchar(200) > > select @proc = rtrim(db_name())+'.dbo.sp_spaceused' > > declare Cur_Cursor cursor local > > for > > select > > TABLE_NAME = > > rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME) > > from > > INFORMATION_SCHEMA.TABLES > > where > > TABLE_TYPE = 'BASE TABLE' > > order by > > 1 > > open Cur_Cursor > > declare @TABLE_NAME varchar(200) > > select @fetch_status = 0 > > while @fetch_status = 0 > > begin > > fetch next from Cur_Cursor > > into > > @TABLE_NAME > > select @fetch_status = @@fetch_status > > if @fetch_status <> 0 > > begin > > continue > > end > > truncate table #TABLE_SPACE_WORK > > insert into #TABLE_SPACE_WORK > > ( > > TABLE_NAME, > > TABLE_ROWS, > > RESERVED, > > DATA, > > INDEX_SIZE, > > UNUSED > > ) > > exec @proc @objname = > > @TABLE_NAME > > update #TABLE_SPACE_WORK > > set > > TABLE_NAME = @TABLE_NAME > > insert into #TABLE_SPACE_USED > > ( > > TABLE_NAME, > > TABLE_ROWS, > > RESERVED, > > DATA, > > INDEX_SIZE, > > UNUSED > > ) > > select > > TABLE_NAME, > > TABLE_ROWS, > > RESERVED, > > DATA, > > INDEX_SIZE, > > UNUSED > > from > > #TABLE_SPACE_WORK > > end --While end > > close Cur_Cursor > > deallocate Cur_Cursor > > insert into #TABLE_SPACE > > ( > > TABLE_NAME, > > TABLE_ROWS, > > RESERVED, > > DATA, > > INDEX_SIZE, > > UNUSED, > > USED_MB, > > USED_GB, > > AVERAGE_BYTES_PER_ROW, > > AVERAGE_DATA_BYTES_PER_ROW, > > AVERAGE_INDEX_BYTES_PER_ROW, > > AVERAGE_UNUSED_BYTES_PER_ROW > > ) > > select > > TABLE_NAME, > > TABLE_ROWS, > > RESERVED, > > DATA, > > INDEX_SIZE, > > UNUSED, > > USED_MB = > > round(convert(numeric(25,10),RESERVED)/ > > convert(numeric(25,10),1024),4), > > USED_GB = > > round(convert(numeric(25,10),RESERVED)/ > > convert(numeric(25,10),1024*1024),4), > > AVERAGE_BYTES_PER_ROW = > > case > > when TABLE_ROWS <> 0 > > then round( > > (1024.000000*convert(numeric(25,10),RESERVED))/ > > convert(numeric(25,10),TABLE_ROWS),5) > > else null > > end, > > AVERAGE_DATA_BYTES_PER_ROW = > > case > > when TABLE_ROWS <> 0 > > then round( > > (1024.000000*convert(numeric(25,10),DATA))/ > > convert(numeric(25,10),TABLE_ROWS),5) > > else null > > end, > > AVERAGE_INDEX_BYTES_PER_ROW = > > case > > when TABLE_ROWS <> 0 > > then round( > > (1024.000000*convert(numeric(25,10),INDEX_SIZE))/ > > convert(numeric(25,10),TABLE_ROWS),5) > > else null > > end, > > AVERAGE_UNUSED_BYTES_PER_ROW = > > case > > when TABLE_ROWS <> 0 > > then round( > > (1024.000000*convert(numeric(25,10),UNUSED))/ > > convert(numeric(25,10),TABLE_ROWS),5) > > else null > > end > > from > > ( > > select > > TABLE_NAME, > > TABLE_ROWS, > > RESERVED = > > convert(int,rtrim(replace(RESERVED,'KB',''))), > > DATA = > > convert(int,rtrim(replace(DATA,'KB',''))), > > INDEX_SIZE = > > convert(int,rtrim(replace(INDEX_SIZE,'KB',''))), > > UNUSED = > > convert(int,rtrim(replace(UNUSED,'KB',''))) > > from > > #TABLE_SPACE_USED aa > > ) a > > order by > > TABLE_NAME > > select GETDATE() TIME,DB_NAME() DB,* from #TABLE_SPACE order by 7 desc > > > " > > > EXEC ( @sql > > )- Hide quoted text - > > - Show quoted text - Thanks you've flicked my light bulb on! I've put the script in master as an sp_myproc and can call it in any DB. thanks!
From: crosan on 7 May 2010 16:27 What error are you running into, if I recall correctly, ms_foreachdb will only allow so many characters be passed to it as a variable (2000 or 4000, i don't remember which), this may be part of your issue. Also, which part of the query do you intend to run against each database? The create table statements will fail on the second database since they are always be created in tempdb.
From: Sylvain Lafontaine on 7 May 2010 17:32 "nzrdb6" <alex.campbell(a)sqltechconsulting.co.uk> wrote in message news:b344954a-9e78-4195-9aa5-8df1d35b7f96(a)s29g2000yqd.googlegroups.com... > Thanks you've flicked my light bulb on! I've put the script in master > as an sp_myproc and can call it in any DB. thanks! Good but don't use the prefix sp_ . It's a reserved prefix for SQL-Server and might change the behavior of the SP in some subtil way; leading to some hard to find rare bugs. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French)
|
Next
|
Last
Pages: 1 2 Prev: Dividing a problem into parts and can't get the parts back tog Next: GROUP BY in WHERE |