在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'