ALTER Proc Proc_CreateInsert
(
@TableName varchar(50)
)
As
/**//*
Create BY HJ
Create 2007-11-18
*/
Begin
set nocount on
-- 数字型字段
Declare @NumFields varchar(200)
Set @NumFields = ',tinyint,smallint,int,money,float,bit,decimal,smallmoney,bigint,'
-- 字符型字段
Declare @StringFields varchar(200)
Set @StringFields = ',nvarchar,varchar,'
Declare @FieldPar varchar(2000),
@FieldName varchar(50),
@FieldTypeName varchar(50),
@TableFields varchar(2000),
@TableValues varchar(2000),
@FieldXtype int,
@FieldLength int
Set @FieldPar = ''
Set @TableFields = ''
Set @TableValues = ''
Print 'Create Proc Proc_Insert'+@TableName+char(13)
Print '('+char(13)
--- 构造参数开始，测试只有 Money 字段类型一定不可以加 ''，其他的数字型字段可以加''
Declare myCurrsor Cursor For
Select [name],xtype,length from SysColumns where id =(Select id from Sysobjects where [name]=@TableName) and /**//* 去掉标识为种子的主键 */COLUMNPROPERTY([id],[name],'IsIdentity')<>1
Open myCurrsor
Fetch Next from myCurrsor into @FieldName,@FieldXtype,@FieldLength
While @@Fetch_Status = 0
Begin
Set @TableFields = @TableFields+','+@FieldName
Set @TableValues = @TableValues+',@'+@FieldName
Select @FieldTypeName = [name] from dbo.systypes where xtype=@FieldXtype and name<>'sysname'
Select @FieldPar = @FieldPar + ',' + @FieldName +
Case
When CharIndex(','+@FieldTypeName+',',@NumFields)>0 Then ' '+@FieldTypeName+''
When CharIndex(','+@FieldTypeName+',',@StringFields)>0 Then ' '+@FieldTypeName+'('+Convert(varchar(4),@FieldLength)+')'
Else ' '+@FieldTypeName+''
End
+char(13)
Fetch Next from myCurrsor into @FieldName,@FieldXtype,@FieldLength
End
Close myCurrsor
Deallocate myCurrsor
Print SubString(@FieldPar,2,Len(@FieldPar)-1)+char(13)
Set @TableFields =SubString(@TableFields,2,Len(@TableFields)-1)
Set @TableValues =SubString(@TableValues,2,Len(@TableValues)-1)
--- 构造参数结束
Print ')'+char(13)
Print 'AS'+char(13)
Print 'Begin'+char(13)
--- 构造 Insert Into 语句
Print 'Insert Into '+@TableName+'('+@TableFields+')'+char(13)+' values('+@TableValues+')'
---
Print 'End'
End
**********************************
Alter Proc Proc_CreateUpdate
(
@TableName varchar(50)
)
As
/**//*
Create BY HJ
Create 2007-11-18
*/
Begin
-- 数字型字段
Declare @NumFields varchar(200)
Set @NumFields = ',tinyint,smallint,int,money,float,bit,decimal,smallmoney,bigint,'
-- 字符型字段
Declare @StringFields varchar(200)
Set @StringFields = ',nvarchar,varchar,'
Declare @FieldPar varchar(2000),
@FieldName varchar(50),
@FieldTypeName varchar(50),
@Sql varchar(5000),
@FieldXtype int,
@FieldLength int,
@IsKey int,
@KeyField varchar(50)
Set @FieldPar = ''
Set @Sql = ''
Set @KeyField = ''
Print 'Create Proc Proc_Insert'+@TableName+char(13)
Print '('+char(13)
--- 构造参数开始，测试只有 Money 字段类型一定不可以加 ''，其他的数字型字段可以加''
Declare myCurrsor Cursor For
Select [name],xtype,length,isKey=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 1 else 0 end
from SysColumns a where id =(Select id from Sysobjects where [name]='MyUser')
Open myCurrsor
Fetch Next from myCurrsor into @FieldName,@FieldXtype,@FieldLength,@IsKey
While @@Fetch_Status = 0
Begin
if(@IsKey=1)
Set @KeyField = @FieldName
else
Set @Sql = @Sql +','+@FieldName+'=@'+@FieldName
Select @FieldTypeName = [name] from dbo.systypes where xtype=@FieldXtype and name<>'sysname'
Select @FieldPar = @FieldPar + ',' + @FieldName +
Case
When CharIndex(','+@FieldTypeName+',',@NumFields)>0 Then ' '+@FieldTypeName+''
When CharIndex(','+@FieldTypeName+',',@StringFields)>0 Then ' '+@FieldTypeName+'('+Convert(varchar(4),@FieldLength)+')'
Else ' '+@FieldTypeName+''
End
+char(13)
Fetch Next from myCurrsor into @FieldName,@FieldXtype,@FieldLength,@IsKey
End
Close myCurrsor
Deallocate myCurrsor
Print SubString(@FieldPar,2,Len(@FieldPar)-1)+char(13)
Set @Sql =SubString(@Sql,2,Len(@Sql)-1)
--- 构造参数结束
Print ')'+char(13)
Print 'AS'+char(13)
Print 'Begin'+char(13)
--- 构造 Insert Into 语句
Print 'Update '+@TableName+' Set '+@Sql+' where '+@KeyField+'=@'+@KeyField+''
---
Print 'End'
End
************************************
另外收藏一条非常有用的 Sql 语句
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=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,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xusertype=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 sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.name='MyUser' --如果只查询指定表,加上此条件
order by a.id,a.colorder