模仿实现mssql的select语句

--实现ms的SELECT TOP 1000 [ID],[Depart],[Category],[Salary] FROM [Test].[dbo].[Employee]
DECLARE @tbName VARCHAR(50),@selectSql VARCHAR(MAX),@min INT=1,@max INT,@fieldName VARCHAR(100)
IF  EXISTS (SELECT * FROM sysobjects WHERE name = 'TEMP_TB' AND xtype = 'U')
	DROP TABLE TEMP_TB
SET @tbName='Employee'
SET @selectSql='SELECT  序号 =row_number() OVER(ORDER BY  a.colorder ASC) 
						,表名       = 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 parent_obj=a.id 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 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=''Employee'' --特定数据特定表名
					order BY  a.id,a.colorder   --如果只查询指定表,加上此条件order by    a.id,a.colorder'
SELECT * INTO TEMP_TB FROM ( SELECT  序号 =row_number() OVER(ORDER BY  a.colorder ASC) 
									,表名       = 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 parent_obj=a.id 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 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=@tbName --特定数据特定表名
									--order BY  a.id,a.colorder   --如果只查询指定表,加上此条件order by    a.id,a.colorder
						)outerTb
						order BY outerTb.序号 
SELECT @max=count(*) FROM TEMP_TB
SET @selectSql='SELECT '
WHILE @min<=@max
	BEGIN
	SELECT @fieldName=t.字段名 FROM TEMP_TB t WHERE t.序号=@min
		SET @selectSql=@selectSql+quotename(@fieldName,'[')+','
	SET @min=@min+1
	END
SET @selectSql=SUBSTRING(@selectSql,1,LEN(@selectSql)-1)--去掉最后的逗号
SET @selectSql=@selectSql+'FROM '+@tbName
SELECT @selectSql