sybase 执行存储过程 sybase create table as

在sybase中写一个存储过程，获取table的创建语句。

-- [/Formatter] Formatted with Sybase T-SQL Formatter(version: 1.5.1.14696) at 05/22/2019 18:07:46 08:00:00[Formatter/]
IF OBJECT_ID('get_create_sql') IS NOT NULL
	DROP PROCEDURE get_create_sql
GO

CREATE proc get_create_sql(@table_name VARCHAR(20))
AS
DECLARE @str VARCHAR(600)
	,@name VARCHAR(40)
	,@type VARCHAR(40)
	,@isnull VARCHAR(40)

BEGIN
	IF OBJECT_ID('#tmp') IS NOT NULL
		DROP TABLE #tmp

	CREATE TABLE #tmp(
		name VARCHAR(40) NULL
		,type VARCHAR(40) NULL
		,isnull VARCHAR(40) NULL
		)

	INSERT INTO #tmp
	SELECT a.name
		,c.name
		,CASE isnull(a.status, 0)
			WHEN 0
				THEN 'NOT NULL'
			ELSE 'NULL'
			END
	FROM syscolumns a
		,sysobjects b
		,systypes c
	WHERE a.id = b.id
		AND a.usertype = c.usertype
		AND b.name = @table_name

	SET @str = "create table " + @table_name + "( "

	DECLARE mo_turn CURSOR
	FOR
	SELECT name
		,type
		,isnull
	FROM #tmp

	OPEN mo_turn

	FETCH mo_turn
	INTO @name
		,@type
		,@isnull

	WHILE (@@sqlstatus = 0)
	BEGIN
		SET @str = @str + @name + '  ' + @type + ' ' + @isnull + ', '

		FETCH mo_turn
		INTO @name
			,@type
			,@isnull
	END

	SET @str = @str + ")"

	close mo_turn

	deallocate cursor mo_turn

	SELECT @str
END

调用

exec get_create_sql 'manager'