假设我创建表table1
表table1里面有字段a,b,c,memo这四个字段现在我想写个函数,在函数里面输入table1
得到结果如下 select a,b,c,memo from table1输入每个不同的表都能得出相应多少字段的查询语句
表table1里面有字段a,b,c,memo这四个字段现在我想写个函数,在函数里面输入table1
得到结果如下 select a,b,c,memo from table1输入每个不同的表都能得出相应多少字段的查询语句
select name from syscolumns where id=object_id('TableName')
@tbname varchar(30)
as
exec ('select * from '+@tbname)
proc 比较好
函数动态不了
create proc pr_test
@tb varchar(100)
as
begin
declare @s varchar(100)
select @s = isnull(@s+',',''+'['+name +']' from syscolumns where id = object_id(@tb)
exec('select '+@s+' from '+@tb)
end
go
returns varchar(200)
as
begin
declare @s varchar(100)
select @s = isnull(@s+',','')+'['+name +']' from sys.syscolumns where id=object_id(@tb)
return 'select '+@s+' from '+@tb+''
end
select dbo.get_col('tb')
create function get_col(@tb varchar(100))
returns varchar(200)
as
begin
declare @s varchar(100)
select @s = isnull(@s+',','')+'['+name +']' from sys.syscolumns where id=object_id(@tb)
return 'select '+@s+' from '+@tb+''
end
select dbo.get_col('tb')up
create function get_col(@tb varchar(100))
returns varchar(200)
as
begin
declare @s varchar(100)
select @s = isnull(@s+',','')+'['+name +']' from sys.syscolumns where id=object_id(@tb)
return @s
endgo
select dbo.get_col('ta')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[id],[a],[b],[c](1 行受影响)drop function get_col
这是xiequan2兄的杰作,其实我有点不理解:石头哥说的,函数动态不了。是不是就是不能像下面这样执行?declare @tbname varchar(5)
set @tbname='tb'
exec('select * from'+@tbname)
如果这样的话:那下面这句不算是动态吗?select @s = isnull(@s+',','')+'['+name +']' from sys.syscolumns where id=object_id(@tb)
create table tb
(
ID int identity(1,1) primary key,
[Name] varchar(20),
Keyword varchar(30)
)
insert into tb select 'ws_hgo','hgo'
union all
select 'guo','guoguo'
union all
select 'ws','guo'create proc proc_tb
(
@TableName varchar(30)
)
as
declare @s varchar(100)
select @s = isnull(@s+',','')+'['+name +']' from syscolumns where id = object_id(@TableName)
exec('select '+@s+' from '+@TableName) exec proc_tb 'tb'
ID Name Keyword
----------- -------------------- ------------------------------
1 ws_hgo hgo
2 guo guoguo
3 ws guo