SQL Server Performance Forum – Threads Archive

Script to create all indexes dropped.

Hi friends,
Can any body give me any stored procedure for a script to create all the indexes of a database? The purpose is to drop and create indexes for copying of tables.

---

I am also interested to know a stored procedure to create a script for ‘creating all the indexes’ before dropping the indexes of a database.

---

Here is a stored procedure I use to generate script for all the tables (along with indexes and constraints) of a database. You need to modify it a bit to generate script for only indexes. CREATE PROC spa_GenerateScript (
@uname varchar(30) = null,
@pwd varchar(30) = null,
@dbname varchar(50) = null,
@path varchar(200) = ‘E:MSSQLackupScripts’,
@filename varchar(50) = ‘Script’,
@server varchar(30) = null
)
AS DECLARE @object int
DECLARE @hr int
DECLARE @return varchar(200)
DECLARE @exec_str varchar(200)
DECLARE @tbname varchar(50) set @filename = @filename + (convert(varchar,getdate(),12))+ ‘.sql’ SET NOCOUNT ON — Set the server to the local server
IF @server is NULL
SELECT @server = @@servername — Create an object that points to the SQL Server
EXEC @hr = sp_OACreate ‘SQLDMO.SQLServer’, @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @hr
RETURN
END — Connect to the SQL Server
IF (@uname is NULL) AND (@pwd is NULL)
BEGIN
— Windows NT Authentication mode is used
EXEC @hr = sp_OAMethod @object, ‘Connect’, NULL, @server
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @hr
RETURN
END
END
ELSE
IF (@uname is NULL)
BEGIN
— Set the username to the current user name
SELECT @uname = SYSTEM_USER
EXEC @hr = sp_OAMethod @object,’Connect’,NULL,@server,@uname,@pwd
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @hr
RETURN
END
END
ELSE
IF (@pwd is NULL)
BEGIN
EXEC @hr = sp_OAMethod @object, ‘Connect’, NULL, @server, @uname
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @hr
RETURN
END
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @object,’Connect’,NULL,@server,@uname,@pwd
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @hr
RETURN
END
END — Verify the connection
EXEC @hr = sp_OAMethod @object, ‘VerifyConnection’, @return OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @hr
RETURN
END — Set the database to the current database
IF @dbname is NULL
SELECT @dbname = db_name()
exec(‘exec master..xp_cmdshell ”mkdir ‘ [email protected] + @dbname+ ””)
— Cursor for scripting Tables SET @exec_str = ‘DECLARE script_cursor CURSOR FOR SELECT name FROM ‘
+ @dbname + ‘..sysobjects WHERE type = ”U” and uid = 1 ORDER BY Name’
EXEC (@exec_str) OPEN script_cursor
FETCH NEXT FROM script_cursor INTO @tbname
WHILE (@@fetch_status <> -1)
BEGIN SET @exec_str = ‘Databases("’+ @dbname +’").Tables("’
+ RTRIM(UPPER(@tbname))+’").Script(74077,"’
+ @path + @dbname + ‘Tbl’+ @filename +’")’ EXEC @hr = sp_OAMethod @object, @exec_str, @return OUT
IF @hr <> 0
BEGIN
Select ‘Table Script failed’
EXEC sp_OAGetErrorInfo @object, @hr
RETURN
END
FETCH NEXT FROM script_cursor INTO @tbname
END
CLOSE script_cursor
DEALLOCATE script_cursor — Destroy the object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @hr
RETURN
END

---

Also refer to this topic http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1382 and follow Gaurav’s link. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

If you are going to script "all the indexes" in your database (not on an everyday basis, I suppose) what is wrong with using the "Generate Script" menu option in Enterprise Manager and then selecting to script all indexes? Nathan H.O.
Moderator
SQL-Server-Performance.com

---

what about for everyday basis?

---

]]>