Create Procedure sp_Script_Indexes (@DBName Varchar(30),@TBCreator
Varchar(30),@DSName VarChar(255)='D:\Win32App\MSSql\Backup\index.sql') With
Recompile
as

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @CallStr varchar(255)
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)



SET NOCOUNT ON
  
-- Create an object that points to the SQL Server
EXEC @hr = sp_OACreate 'SQLOLE.SQLServer', @object OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  
  
--  Connect to the sql server
EXEC @hr = sp_OAMethod @object, 'Connect', NULL
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  
-- Verify that the connection is OK
EXEC @hr = sp_OAMethod @object, 'VerifyConnection', @return OUT
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END

Select @CallStr="DECLARE tnames_cursor CURSOR FOR SELECT name FROM "+
                 @DBName+"..sysobjects WHERE type = 'U' order by Name"

Exec (@CallStr)

--DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects 
-- WHERE type = 'U' order by Name

OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN


    SELECT @CallStr =
'Databases("'+@DBName+'").Tables("'+@TBCreator+'.'+RTRIM(UPPER(@tablename))+
'").Script(74056,"'+@DSName+'")'

    EXEC @hr = sp_OAMethod @object, @CallStr , @return OUT
    IF @hr <> 0
    BEGIN
      EXEC sp_displayoaerrorinfo @object, @hr
      RETURN     END

  END
  FETCH NEXT FROM tnames_cursor INTO @tablename
END
DEALLOCATE tnames_cursor



  
-- Destroy the object EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN
END
  
SET NOCOUNT OFF
GO

GRANT  EXECUTE  ON dbo.sp_Script_Indexes  TO public
GO