USE master
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_scripttable'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_scripttable
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_scripttable'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_scripttable >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_scripttable >>>'
END
go
CREATE PROCEDURE sp_scripttable (
        @dbname nvarchar(128),
        @tablename nvarchar(256)
)
-- WITH ENCRYPTION
AS
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    09 February 1999                                        */
/*      Description:    This SP demonstrates how to use SQLDMO from T-SQL. This */
/*                      can be used to get the CREATE TABLE statement for a tbl.*/
/*                      This is a simple example and to see the entire output of*/
/*                      the SP, change the "Maximum Characters" to be displayed */
/*                      in ISQL/W.                                              */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
DECLARE @local int, @db int, @table int, @retcode int, @method varchar(255)
EXEC @retcode = sp_OACreate 'SQLDMO.SQLServer', @local OUT
IF @retcode <> 0 GOTO ErrorHandler

EXEC @retcode = sp_OAMethod @local, 'Connect', NULL, '(local)'
IF @retcode <> 0 GOTO ErrorHandler

SET @method = 'Databases("' + @dbname + '")'
EXEC @retcode = sp_OAMethod @local, @method, @db OUT
IF @retcode <> 0 GOTO ErrorHandler

SET @method = 'Tables("' + @tablename + '")'
EXEC @retcode = sp_OAMethod @db, @method, @table OUT
IF @retcode <> 0 GOTO ErrorHandler

SET @method = 'Script'
EXEC @retcode = sp_OAMethod @table, @method
IF @retcode <> 0 GOTO ErrorHandler

GOTO Cleanup

ErrorHandler:
    EXEC sp_displayoaerrorinfo @local, @retcode

Cleanup:
EXEC sp_OADestroy @local
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_scripttable'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_scripttable To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_scripttable >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_scripttable >>>'
go

This page was last updated on May 01, 2006 04:28 PM.