Sql Server Job against multiple databases

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

General SQL Server Forums

New to SQL Server Programming

Sql Server Job against multiple databases

Author

Topic

rowter
Yak Posting Veteran

76 Posts

Posted - 2015-05-05 : 14:28:46

Hi,

I want to drop indexes and recreate indexes(non clustered) on all tables in my database. This will run at regular intervals. I created a job for this purpose.
However, in a job i can select only a single database and the job will run only against that database.

How can i make it run against multiple databases?

Thanks

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)

DECLARE dropindexes CURSOR FOR
SELECT indexes.name, objects.name, schemas.name
FROM sys.indexes
JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE indexes.index_id > 0
AND indexes.index_id < 255
AND objects.is_ms_shipped = 0
AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
ORDER BY objects.OBJECT_ID, indexes.index_id DESC

SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
PRINT @sql
EXEC sp_executesql @sql
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tblOne')
BEGIN
CREATE NONCLUSTERED INDEX IX_NC_tbl1col1 ON dbo.tblOne (Col1)
CREATE NONCLUSTERED INDEX IX_NC_tbl1col2 ON dbo.tblOne (Col2)
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-05 : 14:59:51

Add an outer loop to loop through sys.databases or utilize sp_MSforeachdb (or whatever it's called).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2015-05-06 : 04:41:13

How are you going to know what columns, in each table, the indexes were on? What about any options set on the indexes - such as NOT filling each page to 100%? and whether they are UNIQUE or not?

I presume this is not just to restructure the indexes? If so there are REBUILD and REORGANISE commands that you can use instead which will tidy up the structure, but leave the indexes in their original configuration.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources