34,838
社区成员




sp_MSforeachtable
@command1='declare @s varchar(8000)
select @s = isnull(@s+'' or '','''')+a.name +''<0'' from dbo.syscolumns a,sysobjects b where ''[dbo].[''+b.name+'']'' = ''?'' and a.id = b.id and a.xtype in(127,56,52,48,104,108,60,122,62,59)
if @s is not null
begin
select @s = ''if exists(select 1 from ? where ''+@s+'') insert ## select ''''?'''' ''
exec(@s)
end',
@precommand='create table ##(tbn varchar(200))',
@postcommand ='select * from ## drop table ##'
/*
tbn
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[dbo].[os]
[dbo].[Table1]
[dbo].[Table2]
[dbo].[Table3]
[dbo].[A]
[dbo].[B]
[dbo].[T_Line]
[dbo].[RdRecord2]
[dbo].[tt]
[dbo].[thw]
[dbo].[y]
[dbo].[sort2]
*/
得到表中除Col1、Col2的所有列
例如:userno_fm、userno_to
create table test(
num int identity(1,1),
userno_fm varchar(10),
userno_to varchar(10),
username varchar(10))
select * from test
declare @sql varchar(8000)
select @sql=''
select @sql=@sql+','+[name] from
(select [name] from syscolumns where object_id(N'[test]')=[id] and [name] not in ('userno_fm','userno_to')) A
set @sql='select '+stuff(@sql,1,1,'')+' from [test]'
--print @sql
exec (@sql)
drop table test