由于原来使用的是MS SQL SERVER 数据库，想实现在SQL查询分析器里，生成创建达梦数据表的脚本。因此写了一个存储过程，该过程实现输入一个表名，会自动转换为对应的脚本，包括创建表和创建键值（修改表）的脚本输出。这是一个适合自己框架的范例程序，大家有兴趣可以进一步修改。脚本代码如下:
代码语言：javascript复制CREATE PROCEDURE [SqlToDM_CreateTable]
@tablename nvarchar(50)
AS
BEGIN
declare @表名 nvarchar(50)
declare @表说明 nvarchar(50)
declare @字段序号 nvarchar(50)
declare @字段名 nvarchar(50)
declare @标识 nvarchar(50)
declare @主键 nvarchar(50)
declare @类型 nvarchar(50)
declare @占用字节数 nvarchar(50)
declare @长度 nvarchar(50)
declare @小数位数 nvarchar(50)
declare @允许空 nvarchar(50)
declare @默认值 nvarchar(50)
declare @字段说明 nvarchar(50)
DECLARE _cursor CURSOR FOR
SELECT
表名=convert(nvarchar(50),case when a.colorder=1 then d.name else '' end),
表说明=convert(nvarchar(50),case when a.colorder=1 then isnull(f.value,'') else '' end),
字段序号=convert(nvarchar(50),a.colorder),
字段名=convert(nvarchar(50),a.name),
标识=convert(nvarchar(50),case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end),
主键=convert(nvarchar(50),case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end),
类型=convert(nvarchar(50),b.name),
占用字节数=convert(nvarchar(50),a.length),
长度=convert(nvarchar(50),COLUMNPROPERTY(a.id,a.name,'PRECISION')),
小数位数=convert(nvarchar(50),isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)),
允许空=convert(nvarchar(50),case when a.isnullable=1 then 'NULL'else 'NOT NULL' end),
默认值=convert(nvarchar(50),isnull(e.text,'')),
字段说明=convert(nvarchar(50),isnull(g.[value],''))
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
where d.name=@tablename
order by a.id,a.colorder
OPEN _cursor;
FETCH NEXT FROM _cursor INTO @表名,@表说明,@字段序号,@字段名,@标识,@主键,@类型,@占用字节数,@长度,@小数位数,@允许空,@默认值,@字段说明
DECLARE @result nvarchar(max)='CREATE TABLE ' @tablename '('
DECLARE @key nvarchar(max)=''
WHILE @@FETCH_STATUS = 0
BEGIN
set @长度='(' @长度 ') '
if @类型='uniqueidentifier'
begin
set @类型='NCHAR'
set @长度='(36)'
end
else if CHARINDEX('char',@类型)>0 and CHARINDEX('-1',@长度)>0
begin
set @类型='LONGVARCHAR'
set @长度=''
end
else if CHARINDEX('int',@类型)>0 or CHARINDEX('date',@类型)>0
or CHARINDEX('bit',@类型)>0 or CHARINDEX('image',@类型)>0
begin
set @长度=''
end
else if CHARINDEX('money',@类型)>0
begin
set @类型='DECIMAL'
set @长度='(19,4)'
end
else if @小数位数<>'0'
begin
set @长度='(' @长度 ',' @小数位数 ')'
end
set @result=@result @字段名 ' ' @类型 @长度 ' ' @允许空 ','
--if(@主键='√')
--begin
-- set @key=@key 'NOT CLUSTER PRIMARY KEY(' @字段名 ')' char(10)
-- end
FETCH NEXT FROM _cursor INTO @表名,@表说明,@字段序号,@字段名,@标识,@主键,@类型,@占用字节数,@长度,@小数位数,@允许空,@默认值,@字段说明
--,@字段名,@标识,@主键,@类型,@占用字节数,@长度,@小数位数,@允许空,@默认值,@字段说明
END
-- CREATE UNIQUE INDEX "3333" ON "SYSDBA"."APIURLLIST"("URL" ASC,"DESCRIPT" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
CLOSE _cursor;
DEALLOCATE _cursor;
if @key=''
begin
set @result=substring(@result,1,len(@result)-1)
end
select @result @key ');'
DECLARE _cursor2 CURSOR FOR
SELECT a.name IndexName,d.COLUMN_NAME,d.ORDINAL_POSITION,a.type_desc,convert(char(1),a.is_primary_key) is_primary_key
FROM sys.indexes a (NOLOCK)
INNER JOIN sys.index_columns b (NOLOCK) ON a.object_id = b.object_id and a.index_id = b.index_id
INNER JOIN sysindexkeys c (NOLOCK) ON a.object_id = c.id and b.index_id = c.indid and b.column_id = c.colid
INNER JOIN INFORMATION_SCHEMA.COLUMNS d (NOLOCK) ON a.object_id = object_id(d.TABLE_NAME) and b.column_id = d.ORDINAL_POSITION
WHERE a.object_id = object_id(@tablename)
/*and a.is_primary_key = 1 主键索引*/
/*and a.is_unique = 1 唯一索引*/
ORDER BY a.name,b.key_ordinal
OPEN _cursor2;
declare @IndexName nvarchar(50)
declare @COLUMN_NAME nvarchar(50)
declare @ORDINAL_POSITION nvarchar(50)
declare @type_desc nvarchar(50)
declare @is_primary_key char(1)
FETCH NEXT FROM _cursor2 INTO @IndexName,@COLUMN_NAME,@ORDINAL_POSITION,@type_desc,@is_primary_key
DECLARE @result2 nvarchar(max)=''
DECLARE @indextemp nvarchar(50)=''
DECLARE @indexcontent nvarchar(max)=''
WHILE @@FETCH_STATUS = 0
BEGIN
if @indextemp<>@IndexName
BEGIN
set @indextemp=@IndexName
if(@result2<>'')
begin
if substring(@result2,len(@result2),1)=','
begin
set @result2=substring(@result2,1,len(@result2)-1) ');'
end
-- set @result2=@result2 ')'
end
if @is_primary_key='1'
begin
set @result2=@result2 'ALTER TABLE ' @tablename ' add constraint ' @IndexName ' primary key (' @COLUMN_NAME ' ,'
end
else
begin
set @result2=@result2 'CREATE UNIQUE INDEX "' @IndexName '" ON ' @tablename '(' @COLUMN_NAME ' ,'
end
END
ELSE
BEGIN
set @result2=@result2 '"' @COLUMN_NAME '" ,'
END
FETCH NEXT FROM _cursor2 INTO @IndexName,@COLUMN_NAME,@ORDINAL_POSITION,@type_desc,@is_primary_key
END
CLOSE _cursor2;
DEALLOCATE _cursor2;
if substring(@result2,len(@result2),1)=','
begin
set @result2=substring(@result2,1,len(@result2)-1) ')'
end
select @result2
END
GO创建完存储过程，执行如下语句即可： SqlToDM_CreateTable '你的表名'
部分提示要点：达梦的表没有uniqueidentifier类型，将转为NCHAR(36)。money会转为DECIMAL（19，4），其它请参照输出脚本。
