我声明一个游标 用来提取所有用户表的列名
变量@mytable 用来保存准备提取的表的名字
declare fetch_mycolunmsname cursor local scroll
for
select mycolunmsname from object_id(@mytablename)提示对象名 'object_id' 无效要是这么写
declare fetch_mycolunmsname cursor local scroll
for
select mycolunmsname from @mytablename
就提示必须声明变量@mytablename
但是 我在开始就声明过了
就是说我怎么用变量来存储表名 因为我们平时写的语句几乎都是已经指定了一个表名如:select * from table_a
如果表明在不断的变化 那该怎么写??????
变量@mytable 用来保存准备提取的表的名字
declare fetch_mycolunmsname cursor local scroll
for
select mycolunmsname from object_id(@mytablename)提示对象名 'object_id' 无效要是这么写
declare fetch_mycolunmsname cursor local scroll
for
select mycolunmsname from @mytablename
就提示必须声明变量@mytablename
但是 我在开始就声明过了
就是说我怎么用变量来存储表名 因为我们平时写的语句几乎都是已经指定了一个表名如:select * from table_a
如果表明在不断的变化 那该怎么写??????
set @mytablename='ACBOM'
exec('select * from '+@mytablename)
create procedure p_1(@tbname varchar(100))
as
begin
select name from syscolumns where object_id(@tbname)=id order by colorder;
end
insert tab
select 1,'张三'
union all
select 2,'李四'
declare @mytablename varchar(100)
set @mytablename='tab'
exec('select * from '+@mytablename)
drop table tab
(2 行受影响)
ID NAME
----------- ----------
1 张三
2 李四
(2 行受影响)
实在想用游标,要声明两个,不是一个:
*/declare @Table sysname, @Column sysnamedeclare curTab cursor for select name from sysobjects where xtype = 'u'
open curTab
fetch next from curTab into @Table
while @@fetch_status = 0
begin
declare curCol cursor for select name from syscolumns where id = object_id(@Table)
open curCol
fetch next from curCol into @Column
while @@fetch_status = 0
begin
print @Table+'.'+@Column
fetch next from curCol into @Column
end
close curCol
deallocate curCol
fetch next from curTab into @Table
end
close curTab
deallocate curTab
for
select mycolunmsname from object_id(@mytablename)我是在游标中用的 那该怎么实现用变量来保存我要提取列名的表呢???
declare @s varchar(100)
set @s='jobs'exec('select name,id from syscolumns where id=object_id('''+@s+''')')
name id
---------------------------------------------------------------------
job_desc 277576027
job_id 277576027
max_lvl 277576027
min_lvl 277576027
for
select object_id('章节表')
open cur_1
close cur_1
deallocate cur_1