我的SQL是:declare @sql1 nvarchar(100) declare @sql2 nvarchar(100) set @sql1 = 'TableName' set @sql2 = 'ColumnName' declare @sql nvarchar(100) set @slq='select ' + @sql2 + ' from ' + @sql1 exec(@sql) 现在要对50个表进行这个操作,所以要传入50个表名变量和50个列名变量。
表名用字符串set @sql1 = 'TableName...TableName2...' 列名根据表名来动态取 楼主研究下这个,根据表名可以得到表的是有结构,包括字段--功能概述:显示某一表的结构DECLARE @tableName nvarchar(100) SET @tableName ='brand'--mssql2005 SELECT ( CASE WHEN a.colorder=1 THEN d.name ELSE '' END)表名, a.colorder 字段序号, a.name 字段名, (CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END) 标识, (CASE WHEN ( SELECT COUNT(*) FROM sysobjects WHERE (name IN (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid IN (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid IN (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 THEN '√' ELSE '' END) 主键, b.name 类型, a.length 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度, ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数, (CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空, ISNULL(e.text,'') 默认值, ISNULL(g.[value],'') AS 字段说明 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.major_id WHERE d.name=@tableName ORDER BY a.id,a.colorder
--declare @sql1 nvarchar(100) declare @sql2 nvarchar(100) --set @sql1 = 'TableName' set @sql2 = 'ColumnName' declare @sql nvarchar(4000) select @sql=isnull(@sql,'')+' select ' + @sql2 + ' from ' + name from sysobjects where type='U' and name in('tb1','tb2') --50個表名列在裡面 exec( @sql)
declare @sql varchar(4000), @csql varchar(4000), @column varchar(200), @tablename varchar(200), @object_id int declare cur_table cursor for select name,object_id from sys.all_objects where type='U' order by nameopen cur_table fetch next from cur_table into @tablename,@object_id while @@fetch_status=0 begin select @csql ='',@sql ='' declare cur_table_column cursor for select name from sys.columns where object_id = @object_id open cur_table_column fetch next from cur_table_column into @column while @@fetch_status = 0 begin set @csql = @csql+@column+',' fetch next from cur_table_column into @column end close cur_table_column deallocate cur_table_column set @csql =substring(@csql,1,len(@csql)-1) set @sql = 'select '+@csql + ' from ' +@tablename print @sql fetch next from cur_table into @tablename,@object_id end close cur_table deallocate cur_table /* 执行以后 结果 print select BUS_UNIT_CODE,BUS_UNIT_DESC,LOGO_PATH,MinChargeWeekDays,MinChargeHolidays from BUSINESS_UNIT select LogID,EventID,Priority,Severity,Title,Timestamp,MachineName,AppDomainName,ProcessID,ProcessName,ThreadName,Win32ThreadId,Message,FormattedMessage from Log*/
select id=identity(int,1,1),name into #temp from sysobjects where xtype='U'
把你的表和字段存入一个表,然后用动态sql
是不是也可以把我的sql改成存储过程,然后通过参数调用呢?
可以。。 if object_id('p') is not null drop proc p go create proc p @sql1 nvarchar(100), @sql2 nvarchar(100) as declare @sql nvarchar(100) set @sql='select ' + @sql2 + ' from ' + @sql1 exec(@sql) goexec p 'tb','id' /* --- 1 */
exec sp_msforeachtable @command1=N'select * from ? ',@whereand=N' and name in(''表名1'',''表名2'',''表名3'')'
declare @sql2 nvarchar(100) set @sql1 = 'TableName'
set @sql2 = 'ColumnName' declare @sql nvarchar(100)
set @slq='select ' + @sql2 + ' from ' + @sql1
exec(@sql)
现在要对50个表进行这个操作,所以要传入50个表名变量和50个列名变量。
表名用字符串set @sql1 = 'TableName...TableName2...'
列名根据表名来动态取
楼主研究下这个,根据表名可以得到表的是有结构,包括字段--功能概述:显示某一表的结构DECLARE @tableName nvarchar(100)
SET @tableName ='brand'--mssql2005
SELECT (
CASE WHEN a.colorder=1 THEN d.name ELSE '' END)表名,
a.colorder 字段序号,
a.name 字段名,
(CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√' ELSE '' END) 标识,
(CASE WHEN (
SELECT COUNT(*)
FROM sysobjects
WHERE (name IN
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid IN
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid IN
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 THEN '√' ELSE '' END) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度,
ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数,
(CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空,
ISNULL(e.text,'') 默认值,
ISNULL(g.[value],'') AS 字段说明
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.major_id
WHERE d.name=@tableName
ORDER BY a.id,a.colorder
declare @sql2 nvarchar(100)
--set @sql1 = 'TableName'
set @sql2 = 'ColumnName'
declare @sql nvarchar(4000)
select @sql=isnull(@sql,'')+' select ' + @sql2 + ' from ' + name from sysobjects where type='U' and name in('tb1','tb2') --50個表名列在裡面
exec( @sql)
@sql varchar(4000),
@csql varchar(4000),
@column varchar(200),
@tablename varchar(200),
@object_id int
declare
cur_table cursor for
select name,object_id from sys.all_objects
where type='U'
order by nameopen cur_table
fetch next from cur_table into @tablename,@object_id
while @@fetch_status=0
begin
select @csql ='',@sql =''
declare
cur_table_column cursor for
select name from sys.columns
where object_id = @object_id
open cur_table_column
fetch next from cur_table_column into @column
while @@fetch_status = 0
begin
set @csql = @csql+@column+','
fetch next from cur_table_column into @column
end
close cur_table_column
deallocate cur_table_column set @csql =substring(@csql,1,len(@csql)-1)
set @sql = 'select '+@csql + ' from ' +@tablename
print @sql
fetch next from cur_table into @tablename,@object_id
end
close cur_table
deallocate cur_table
/* 执行以后 结果
print
select BUS_UNIT_CODE,BUS_UNIT_DESC,LOGO_PATH,MinChargeWeekDays,MinChargeHolidays from BUSINESS_UNIT
select LogID,EventID,Priority,Severity,Title,Timestamp,MachineName,AppDomainName,ProcessID,ProcessName,ThreadName,Win32ThreadId,Message,FormattedMessage from Log*/
if object_id('p') is not null
drop proc p
go
create proc p
@sql1 nvarchar(100),
@sql2 nvarchar(100)
as
declare @sql nvarchar(100)
set @sql='select ' + @sql2 + ' from ' + @sql1
exec(@sql)
goexec p 'tb','id'
/*
---
1
*/