求一个输入表得出字段的函数

[ou630j]

假设我创建表table1
表table1里面有字段a,b,c,memo这四个字段

现在我想写个函数,在函数里面输入table1
得到结果如下 select a,b,c,memo from table1

输入每个不同的表都能得出相应多少字段的查询语句

[ws_hgo]

if object_id('tb') is not null drop table 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

[--小F--]

列出表里的所有的列 
select name from syscolumns where id=object_id('TableName') 

[csdyyr]

只能用存储过程。

[feixianxxx]

create proc  k 
@tbname varchar(30)
as
exec ('select * from '+@tbname)

proc 比较好

[--小F--]

select count(name) from syscolumns where id=object_id('TableName') 

[-狙击手-]

改成过程吧
函数动态不了
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

[ICrazyBoy]

把所有的值放进你定义的一个表阿!把所有的值插入到一个表当中!建议存储过程比叫好点!定义一个临时表#table 然后把所有的数据放入就进去就好了哦!

[xiequan2]

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')
 

[华夏小卒]

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

[SQL77]

[Quote=引用 5 楼 happyflystone 的回复:]
改成过程吧
函数动态不了
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

[/Quote]
顶这个

[华夏小卒]

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
end

go
select dbo.get_col('ta')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[id],[a],[b],[c]

(1 行受影响)

drop function get_col

[htl258_Tony]

[Quote=引用 10 楼 js_szy 的回复:]
SQL codecreatefunction get_col(@tbvarchar(100))returnsvarchar(200)asbegindeclare@svarchar(100)select@s=isnull(@s+',','')+'['+name+']'from sys.syscolumnswhere id=object_id(@tb)return@sendgoselect dbo.g¡­
[/Quote]
鉴定可用.

[华夏小卒]

[Quote=引用 11 楼 htl258 的回复:]
引用 10 楼 js_szy 的回复:
SQL codecreatefunction get_col(@tbvarchar(100))returnsvarchar(200)asbegindeclare@svarchar(100)select@s=isnull(@s+',','')+'['+name+']'from sys.syscolumnswhere id=object_id(@tb)return@sendgoselect dbo.g¡­

鉴定可用.
[/Quote]

这是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)

[SQL77]

[Quote=引用 12 楼 js_szy 的回复:]
引用 11 楼 htl258 的回复:
引用 10 楼 js_szy 的回复:
SQL codecreatefunction get_col(@tbvarchar(100))returnsvarchar(200)asbegindeclare@svarchar(100)select@s=isnull(@s+',','')+'['+name+']'from sys.syscolumnswhere id=object_id(@tb)return@sendgoselect dbo.g¡­

鉴定可用.


这是xiequan2兄的杰作，

其实我有点不理解：石头哥说的，函数动态不了。是不是就是不能像下面这样执行?
SQL codedeclare@tbnamevarchar(5)set@tbname='tb'exec('select * from'+@tbname)

如果这样的话：那下面这句不算是动态吗？
SQL codeselect@s=isnull(@s+',','')+'['+name+']'from sys.syscolumnswhere id=object_id(@tb)

[/Quote]
HOHO,就是不能EXEC()

[htl258_Tony]

[Quote=引用 12 楼 js_szy 的回复:]
引用 11 楼 htl258 的回复:
引用 10 楼 js_szy 的回复:
SQL codecreatefunction get_col(@tbvarchar(100))returnsvarchar(200)asbegindeclare@svarchar(100)select@s=isnull(@s+',','')+'['+name+']'from sys.syscolumnswhere id=object_id(@tb)return@sendgoselect dbo.g¡­

鉴定可用.


这是xiequan2兄的杰作，

其实我有点不理解：石头哥说的，函数动态不了。是不是就是不能像下面这样执行?
SQL codedeclare@tbnamevarchar(5)set@tbname='tb'exec('select * from'+@tbname)

如果这样的话：那下面这句不算是动态吗？
SQL codeselect@s=isnull(@s+',','')+'['+name+']'from sys.syscolumnswhere id=object_id(@tb)

[/Quote]
可以拼出动态语句串,不能执行拼出来的串.

[SQL77]

[Quote=引用 12 楼 js_szy 的回复:]
引用 11 楼 htl258 的回复:
引用 10 楼 js_szy 的回复:
SQL codecreatefunction get_col(@tbvarchar(100))returnsvarchar(200)asbegindeclare@svarchar(100)select@s=isnull(@s+',','')+'['+name+']'from sys.syscolumnswhere id=object_id(@tb)return@sendgoselect dbo.g¡­

鉴定可用.


这是xiequan2兄的杰作，

其实我有点不理解：石头哥说的，函数动态不了。是不是就是不能像下面这样执行?
SQL codedeclare@tbnamevarchar(5)set@tbname='tb'exec('select * from'+@tbname)

如果这样的话：那下面这句不算是动态吗？
SQL codeselect@s=isnull(@s+',','')+'['+name+']'from sys.syscolumnswhere id=object_id(@tb)

[/Quote]
XIEQUAN2的可以返回一个字符串,最后在外面动态执行

[华夏小卒]

o_o
还是基础不扎实啊

[htl258_Tony]

[Quote=引用 5 楼 happyflystone 的回复:]
改成过程吧
函数动态不了
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

[/Quote]
当然,如果想直接得到结果,还是要用存储过程.