November 27, 2012 at 4:02 pm
I know this may seem pointless at best, I'm still working on learning more about SQL scripting. I have a three step process I'm trying to get working. The main thing is that I'd like it to automatically identify the schema associated with the table. below is the working script. Now, when i say working... I mean it works on 90% of my servers minus the two servers we have that have more than one schema... 4-8 each I'd think.
the point is mainly on step 3, I'd like it to ... I guess have another declare that will set the schema association? I'm not sure what table pulls that across. I know I can build a cursor to do this, but I would like to at least prove to my self it can be done without. specifically, we have a working job currently and we are aware of the other scripts that are out there that offer so much more versatility. We just want to, I guess, prove that this can work.
I'd like to also add, it gets a bit twitchy when i try to make it only make and delete the tables in steps 1 and 2 if i add the IF DB_ID(''?'') > 4 statement. which I don't fully understand why it hates it so..
Step 1
exec sp_MSforeachdb @command1 ='
USE [?]
IF OBJECT_ID(N''DBA_Reindex'', N''U'') IS NOT NULL
DROP TABLE DBA_Reindex;'
Step 2
exec sp_MSforeachdb @command1 ='
USE [?]
SELECT distinct object_Name(i.object_id)
AS TableName, i.object_id
into DBA_Reindex
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join sys.indexes as i on p.object_id = i.object_id and p.index_id = i.index_id
where i.index_id > 0 and avg_fragmentation_in_percent > 10 and page_count > 25'
Step 3
exec sp_MSforeachdb @command1 ='
IF DB_ID(''?'') > 4
USE [?]
while ((select top 1 [object_id] From DBA_Reindex order by [object_id] Desc) > 1)
begin
DECLARE @Command VARCHAR(100)
declare @table VarChar(150)
DECLARE @alter VARCHAR(100)
declare @rebuild VarChar(150)
declare @reindex varchar(300)
set @alter = (''ALTER INDEX ALL ON dbo.'')
set @rebuild = ('' REBUILD WITH (FILLFACTOR = 80);'')
set @table = (select top(1) [TableName] from DBA_Reindex order by [object_id] desc)
set @command = @alter + @table + @rebuild
execute (@Command)
update DBA_Reindex set [object_id] = 0 where [object_id] = (select top(1) [object_id] from DBA_Reindex order by [object_id] desc)
end'
.
November 27, 2012 at 4:17 pm
I found the below script works, though i'm still having issues with IF DB_ID(''?'') > 4
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
seems to give me issues as well.
If anyone can think of anything that would make this faster, I'd appreciate it. Currently the table is being left so that the next business day we can verify if they're growing larger as well as see where it failed last.
step 1)
exec sp_MSforeachdb @command1 ='
USE [?]
IF OBJECT_ID(N''DBA_Reindex'', N''U'') IS NOT NULL
DROP TABLE DBA_Reindex;'
step 2)
exec sp_MSforeachdb @command1 ='
USE [?]
SELECT distinct object_Name(i.object_id)
AS TableName, i.object_id,
Schema_Name(o.schema_id)
AS SchemaName
into DBA_Reindex
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join sys.objects as o on p.object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id
inner join sys.indexes as i on p.object_id = i.object_id
and p.index_id = i.index_id
where i.index_id > 0 and avg_fragmentation_in_percent > 10 and page_count > 25'
step 3)
exec sp_MSforeachdb @command1 ='
IF DB_ID(''?'') > 4
USE [?]
while ((select top 1 [object_id] From DBA_Reindex order by [object_id] Desc) > 1)
begin
DECLARE @Command VARCHAR(100)
declare @table VarChar(150)
DECLARE @alter VARCHAR(100)
declare @rebuild VarChar(150)
declare @reindex varchar(300)
declare @schema varchar (100)
declare @dot Varchar(10)
set @alter = (''ALTER INDEX ALL ON '')
set @schema = (select top(1) [SchemaName] from DBA_Reindex order by [object_id] desc)
set @rebuild = ('' REBUILD WITH (FILLFACTOR = 80);'')
set @table = (select top(1) [TableName] from DBA_Reindex order by [object_id] desc)
set @dot = (''.'')
set @command = @alter +@schema + @dot + @table + @rebuild
execute (@Command)
update DBA_Reindex set [object_id] = 0 where [object_id] = (select top(1) [object_id] from DBA_Reindex order by [object_id] desc)
end'
.
November 27, 2012 at 4:23 pm
fluffydeadangel (11/27/2012)
I found the below script works, though i'm still having issues with IF DB_ID(''?'') > 4IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
seems to give me issues as well.
If anyone can think of anything that would make this faster, I'd appreciate it. Currently the table is being left so that the next business day we can verify if they're growing larger as well as see where it failed last.
step 1)
exec sp_MSforeachdb @command1 ='
USE [?]
IF OBJECT_ID(N''DBA_Reindex'', N''U'') IS NOT NULL
DROP TABLE DBA_Reindex;'
step 2)
exec sp_MSforeachdb @command1 ='
USE [?]
SELECT distinct object_Name(i.object_id)
AS TableName, i.object_id,
Schema_Name(o.schema_id)
AS SchemaName
into DBA_Reindex
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join sys.objects as o on p.object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id
inner join sys.indexes as i on p.object_id = i.object_id
and p.index_id = i.index_id
where i.index_id > 0 and avg_fragmentation_in_percent > 10 and page_count > 25'
step 3)
exec sp_MSforeachdb @command1 ='
IF DB_ID(''?'') > 4
USE [?]
while ((select top 1 [object_id] From DBA_Reindex order by [object_id] Desc) > 1)
begin
DECLARE @Command VARCHAR(100)
declare @table VarChar(150)
DECLARE @alter VARCHAR(100)
declare @rebuild VarChar(150)
declare @reindex varchar(300)
declare @schema varchar (100)
declare @dot Varchar(10)
set @alter = (''ALTER INDEX ALL ON '')
set @schema = (select top(1) [SchemaName] from DBA_Reindex order by [object_id] desc)
set @rebuild = ('' REBUILD WITH (FILLFACTOR = 80);'')
set @table = (select top(1) [TableName] from DBA_Reindex order by [object_id] desc)
set @dot = (''.'')
set @command = @alter +@schema + @dot + @table + @rebuild
execute (@Command)
update DBA_Reindex set [object_id] = 0 where [object_id] = (select top(1) [object_id] from DBA_Reindex order by [object_id] desc)
end'
The code is excluding the system databases, master, msdb, model, and tempdb. These databases use the DB_IDs 1-4.
November 27, 2012 at 4:35 pm
i'm trying to ignore the sys databases. i just dont want to create tables on them either if it's not needed IE steps 1 and 2. step 3 still seems off. i have one index listed sys.queue_messages_2093482348 or some odd number that is on a user database and i show that it will not reindex.
i tried the below script to target out indexing sys. items, and i got whole new errors..
exec sp_MSforeachdb @command1 ='
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''reportservertempdb''
USE [?]
while ((select top 1 [object_id] From DBA_Reindex order by [object_id] Desc) > 1)
begin
DECLARE @Command VARCHAR(100)
declare @table VarChar(150)
DECLARE @alter VARCHAR(100)
declare @rebuild VarChar(150)
declare @reindex varchar(300)
declare @schema varchar (100)
declare @dot Varchar(10)
set @alter = (''ALTER INDEX ALL ON '')
set @schema = (select top(1) [SchemaName] from DBA_Reindex order by [object_id] desc)
set @rebuild = ('' REBUILD WITH (FILLFACTOR = 80);'')
set @table = (select top(1) [TableName] from DBA_Reindex order by [object_id] desc)
set @dot = (''.'')
set @command = @alter +@schema + @dot + @table + @rebuild
if (@schema = ''sys'')
print (''Sys Schema found on '' + @table)
else
execute (@Command)
update DBA_Reindex set [object_id] = 0 where [object_id] = (select top(1) [object_id] from DBA_Reindex order by [object_id] desc)
end'
error received
Msg 468, Level 16, State 9, Line 19
Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
the if statement is complicating things too much i think.
.
November 27, 2012 at 4:59 pm
I will admit, I'm a bit stuck on this and possibly over looking something very simple.
using the below script...
i get the below errors (errors first)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
exec sp_MSforeachdb @command1 ='
use [?]
while ((select top 1 [object_id] From DBA_Reindex order by [object_id] Desc) > 1)
begin
DECLARE @Command VARCHAR(100)
declare @table VarChar(150)
DECLARE @alter VARCHAR(100)
declare @rebuild VarChar(150)
declare @reindex varchar(300)
declare @schema varchar (100)
declare @dot Varchar(10)
set @alter = (''ALTER INDEX ALL ON '')
set @schema = (select top(1) [SchemaName] from DBA_Reindex order by [object_id] desc)
set @rebuild = ('' REBUILD WITH (FILLFACTOR = 80);'')
set @table = (select top(1) [TableName] from DBA_Reindex order by [object_id] desc)
set @dot = (''.'')
set @command = @alter + @schema + @dot + @table + @rebuild
execute (@Command)
update DBA_Reindex set [object_id] = 0 where [object_id] = (select top(1) [object_id] from DBA_Reindex order by [object_id] desc)
end'
.
November 27, 2012 at 5:11 pm
ignore the variable names >.>; apparently i have tables with hyphens in them and that just makes everything twitchy. this seems to work for now.
any advise on what i can shorten would be very appreciated.
exec sp_MSforeachdb @command1 ='
use [?]
while ((select top 1 [object_id] From DBA_Reindex order by [object_id] Desc) > 1)
begin
DECLARE @Command VARCHAR(100)
declare @table VarChar(150)
DECLARE @alter VARCHAR(100)
declare @rebuild VarChar(150)
declare @reindex varchar(300)
declare @schema varchar (100)
declare @dot Varchar(10)
declare @stupid Varchar(10)
declare @char varchar(10)
set @stupid = ''[''
set @char = '']''
set @alter = (''ALTER INDEX ALL ON '')
set @schema = (select top(1) [SchemaName] from DBA_Reindex order by [object_id] desc)
set @rebuild = ('' REBUILD WITH (FILLFACTOR = 80);'')
set @table = (select top(1) [TableName] from DBA_Reindex order by [object_id] desc)
set @dot = (''.'')
set @command = (@alter + @schema + @dot + @stupid + @table + @char + @rebuild)
execute (@Command)
update DBA_Reindex set [object_id] = 0 where [object_id] = (select top(1) [object_id] from DBA_Reindex order by [object_id] desc)
end'
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply