谁能提供一些自动生成sql查询语句的资料？类似sql server管理器查询界面那样，可以根据用户操作自动生成sql语句。

Question

转贴：ALTER          PROCEDURE [dbo].[pr_CreateColumnDefs] AS
declare @TableName  varchar(50),
        @ColumnName varchar(50),
        @ColList    varchar(4000),
        @TypeName   varchar(13),
        @Precision  int,
        @Length     int,
        @Scale      smallint,
        @Nullable   smallint,
        @ColumnDef  nvarchar(4000),
        @IsKeyField smallint,
        @KeyFields   varchar(4000),
        @tempString  varchar(100),
        @BussinessName varchar(100),
        @BaseFields varchar(4000),
        @MainTableName varchar(400),
        @QueryName varchar(100),
        @AllFields varchar(4000),
        @FieldIndex integer,
        @DisplayName varchar(100),
        @tempTableName varchar(100),
        @TableType    varchar(10),
        @CheckTableName varchar(50)
 
/* 一个临时表存放字段定义*/       
create table #ColumnList(
TABLE_QUALIFIER sysname ,
TABLE_OWNER sysname ,
TABLE_NAME sysname ,
COLUMN_NAME sysname ,
DATA_TYPE smallint ,
[TYPE_NAME] varchar(13) , 
[PRECISION] int,
[LENGTH] int,
SCALE smallint,
RADIX smallint,
NULLABLE smallint, 
REMARKS varchar(254) ,
COLUMN_DEF nvarchar(2000),
SQL_DATA_TYPE smallint ,
SQL_DATETIME_SUB smallint ,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int ,
IS_NULLABLE varchar(254) ,
IsKeyField tinyint
)/*一个临时表存放主键定义*/
create table #pkeys (
DatabaseName sysname,
Owner sysname,
tableName sysname,
columnName sysname,
keyseq smallint,
pkname sysname
)/*一个临时表存放业务类定义*/
create table #BussinessDef(
[BussinessName] [varchar] (50),/*业务类名称*/
[MainTableName] [varchar] (50),/*主表名*/
[BaseFields] [varchar] (2000),/*所有字段列表*/
[KeyFields] [varchar] (2000),/*关键字段列表*/
)/*一个临时表存放查询定义*/
create table #QueryDef(
[QueryName] [varchar] (50)  ,
[AllFields] [varchar] (4000)
)/*一个临时表存放字段的中文描述*/
create table #ColDescript (
TableName  sysname,
ColumnName sysname,
ColumnDescript   varchar(100)
)/*一个临时表存放外键定义*/
create table #ForeignKeyDefs (
[PKTABLE_QUALIFIER] [nvarchar] (128),
[PKTABLE_OWNER] [nvarchar] (128),
[PKTABLE_NAME] [nvarchar] (128),
[PKCOLUMN_NAME] [nvarchar] (128),
[FKTABLE_QUALIFIER] [nvarchar] (128),
[FKTABLE_OWNER] [nvarchar] (128),
[FKTABLE_NAME] [nvarchar] (128),
[FKCOLUMN_NAME] [varchar] (32),
[KEY_SEQ] [smallint],
[UPDATE_RULE] [smallint],
[DELETE_RULE] [smallint],
[FK_NAME] [nvarchar] (128),
[PK_NAME] [nvarchar] (128),
        [DEFERABLITY] [smallint]
) /*取得数据库所有字段的描述*/
insert into #ColDescript 
select distinct 
       sysobjects.[name] as tablename, 
       syscolumns.[name] as columnname , 
       cast(sysproperties.value as varchar(100)) as [descript] 
From 
sysobjects,syscolumns ,sysproperties
where 
sysobjects.id = syscolumns.id  and 
sysproperties.id=sysobjects.id and
sysproperties.smallid = syscolumns.colid/*选择本数据库中的所有表和视图*/
declare crTableList cursor FAST_FORWARD READ_ONLY For
        select [name],[type] From [dbo].[sysobjects] where type='U' or type='V' 
        order by TYPEOpen crTableList/*取得一个表的名称和表的类型*/
fetch next from crTableList into @TableName,@TableTypewhile (@@FETCH_STATUS <> -1)
begin
     /*取得表@TableName的主键值列表*/
     insert into #pkeys exec sp_pkeys @TableName
     /*取得表@TableName的列定义临时列表*/
     insert into #ColumnList exec sp_columns @TableName
     /*取得表@TableName的所有外键列表*/
     insert into #ForeignKeyDefs exec sp_fkeys @TableName
     
     /*取得本表各列的游标*/
     declare crCols cursor FAST_FORWARD READ_ONLY for
     Select COLUMN_NAME From #ColumnList
     Where TABLE_NAME=@TableName Order by Ordinal_Position
 
     Open crCols     fetch next from crCols into @ColumnName     Select @ColList = ''
     Select @KeyFields = ''     while (@@FETCH_STATUS <> -1)
     begin          if EXISTS(Select * from #pkeys where tablename = @TableName and ColumnName = @ColumnName ) 
              Select @IsKeyField = 1 
          else 
              Select @IsKeyField = 0          Select @ColList = @ColList + @TableName + '.' +@ColumnName + ';'          if @IsKeyField = 1
               Select @KeyFields =  @TableName + '.' + @ColumnName + ';' + @KeyFields           Update #ColumnList Set IsKeyField = @IsKeyField where Table_Name = @TableName and 
          Column_Name = @ColumnName          fetch next from crCols into @ColumnName     end
     
     if @TableType = 'U' 
     /*如果是用户表*/
     begin
            /*插入一条业务类定义纪录*/
            insert into #BussinessDef(BussinessName,MainTableName,BaseFields,KeyFields) 
            values (@TableName,@TableName,@ColList,@KeyFields)       
            /*插入一条查询定义纪录*/
            insert into #QueryDef(QueryName,AllFields)
            values (@TableName,@ColList)   
     end
     else
     /*如果是视图*/
     begin
            /*插入一条查询定义纪录，主要用于业务填写表格界面*/
            insert into #QueryDef(QueryName,AllFields)
            values (@TableName,@ColList)
            /* 插入另外一条查询定义纪录，后缀为Query,主要用于查询报表*/
            insert into #QueryDef(QueryName,AllFields)
            values (@TableName+'Query',@ColList)
     end
    
     close crCols
     deallocate crCols
     
     fetch next from crTableList into @TableName,@TableType
     Delete #pkeysend
close crTableList
deallocate crTableList/*
[FieldName] [nvarchar] [TableName] [Value] [OldValue] 
        [DisplayName] [DataType] [FieldType] 
[FieldLength] [AllowNull] 
[Precision]  [Scale]  [DefaultValue] 
[IsKeyField] [QueryName]
*/declare crFieldDefinition cursor FAST_FORWARD READ_ONLY For
select table_Name + '.' + column_name, table_name,ORDINAL_POSITION,[type_name],
length,Nullable,[precision],scale,column_def,IsKeyField  From #ColumnList Order by Ordinal_Position

Answer 1

Open crFieldDefinitionfetch next from crFieldDefinition into @ColumnName,@TableName,@FieldIndex,@TypeName,
@Length,@Nullable,@Precision,@scale,@ColumnDef,@IsKeyFieldwhile (@@FETCH_STATUS <> -1)
begin     Select @DisplayName = isnull((
            Select Distinct ColumnDescript From #ColDescript 
            Where (
                   Upper(TableName)=Upper(@TableName) and 
                   Upper( TableName + '.' + ColumnName)  = Upper(@ColumnName)
                  ) or
                  (
                   Upper(TableName+'View')=Upper(@TableName) and 
                   Upper( TableName+'View' + '.' + ColumnName) = Upper(@ColumnName)
                  )                 
                  ),'')    Select @QueryName = isnull((
           Select Distinct PKTABLE_NAME From #ForeignKeyDefs
           Where (
                   Upper(FKTABLE_NAME)=Upper(@TableName) and 
                   Upper( FKTABLE_NAME + '.' + FKCOLUMN_NAME)  = Upper(@ColumnName)
                  ) or
                  (
                   Upper(FKTABLE_NAME+'View')=Upper(@TableName) and 
                   Upper( FKTABLE_NAME+'View' + '.' + FKCOLUMN_NAME) = Upper(@ColumnName) 
                  )
                  )
                  ,'')
         if (Select Count(*) From FieldDefinition where FieldName = @ColumnName) < 1 
     insert into FieldDefinition(FieldName,TableName,FieldIndex,DataType,FieldLength,AllowNull,
     [Precision],scale,DefaultValue,IsKeyField,DisplayName,QueryName) values 
     (@ColumnName,@TableName,@FieldIndex,@TypeName,@Length,@Nullable,@Precision,@scale,@ColumnDef,
     @IsKeyField,@DisplayName,@QueryName)
     else 
     update FieldDefinition set FieldName=@ColumnName,TableName=@TableName,FieldIndex=@FieldIndex,
     DataType=@TypeName,FieldLength=@Length,AllowNull=@Nullable,[Precision]=@Precision,
     scale=@scale,DefaultValue=@ColumnDef,IsKeyField=@IsKeyField ,DisplayName=@DisplayName,
     QueryName=@QueryName
     where FieldName=@ColumnName     fetch next from crFieldDefinition into @ColumnName,@TableName,@FieldIndex,@TypeName,
     @Length,@Nullable,@Precision,@scale,@ColumnDef,@IsKeyFieldendclose crFieldDefinition
deallocate crFieldDefinition/*********************************/
declare  crBussinessDef cursor FAST_FORWARD READ_ONLY for 
select [BussinessName] ,[MainTableName] ,[BaseFields] ,[KeyFields]
From #BussinessDefOpen crBussinessDef
fetch next from crBussinessDef into @BussinessName,@TableName,@BaseFields,@KeyFields
while (@@FETCH_STATUS <> -1)
begin     if (Select Count(*) From BussinessDef where BussinessName = @BussinessName) < 1 
     insert into BussinessDef(BussinessName,MainTableName,BaseFields,Keyfields)
     values(@BussinessName,@TableName,@BaseFields,@KeyFields)
     else 
     update BussinessDef set BussinessName=@BussinessName, MainTableName=@TableName,
     BaseFields=@BaseFields,KeyFields=@KeyFields
     where BussinessName = @BussinessName     fetch next from crBussinessDef into @BussinessName,@TableName,@BaseFields,@KeyFieldsendclose crBussinessDef
deallocate crBussinessDef
/**************************************/
declare  crQueryDef cursor FAST_FORWARD READ_ONLY for 
select [QueryName],[AllFields]  From #QueryDefopen crQueryDef
fetch next from crQueryDef into @QueryName ,@AllFields
while (@@FETCH_STATUS <> -1)
begin     Select @CheckTableName=Upper(@QueryName)
     if not (
                (@QueryName='DOMAINQUERY') 
             or (@QueryName='SYSTEMUSERVIEWQUERY')
             or (@QueryName='VOUCHERVIEWQUERY')
             or (@QueryName='ASSETCOMPANYVIEWQUERY')
            )
     /*有些表的QueryDef要手工填写，不允许自动填写*/
     begin         
          if (Select Count(*) From QueryDef where QueryName = @QueryName) < 1 
          insert into QueryDef(QueryName,AllFields) values(@QueryName,@AllFields)
          else 
          update QueryDef set AllFields=@AllFields
          where QueryName = @QueryName
     end     fetch next from crQueryDef into @QueryName ,@AllFields
end
close crQueryDef
deallocate crQueryDef                               
drop table #ColumnList
drop table #BussinessDef
drop table #QueryDef
drop table #ColDescript
drop table #ForeignKeyDefs

Answer 2

转贴根据基本表结构及其数据生成 INSERT ... 的 SQL 语句
create  proc spGenInsertSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = 'orders'
--set @TableName = 'eeducation'
DECLARE xCursor CURSOR FOR
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
declare @F1 varchar(100)
declare @F2 integer
declare @SQL varchar(8000)
set @sql ='SELECT ''INSERT INTO ' + @TableName + ' VALUES('''
OPEN xCursor
FETCH xCursor into @F1,@F2
WHILE @@FETCH_STATUS = 0
BEGIN
    set @sql =@sql +
              + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + '  else '+' end
              + 'replace(ISNULL(cast(' + @F1 + ' as varchar),''NULL''),'''''''','''''''''''')' 
              + case when @F2 IN (35,58,99,167,175,231,239,61) then ' + case when ' + @F1 + ' IS NULL then '''' else '''''''' end + '  else '+' end
              + char(13) + ''',''' 
    FETCH NEXT FROM xCursor into @F1,@F2
END
CLOSE xCursor
DEALLOCATE xCursor
set @sql = left(@sql,len(@sql) - 5) + ' + '')'' FROM ' + @TableName
print @sql
exec (@sql)

Answer 3

真的好长
copy 一下
研究

Answer 4

非常感谢个各位的解答，但是如果用户操作涉及到表的内联，外联，以及sum , group by 之类的东西，如何自动生成呢？