SELECT name FROM syscolumns WHERE (id IN (SELECT id FROM sysobjects WHERE (name = 'A'))) AND (name NOT IN ('fieldi')) 得到一个你字段的集合,你组个串,然后配个SQL语句 循环配到,例如@str='field2,field3,....fieldn' @SQL= 'select '+@str+' from A ' exec @sql
楼上的方法不错,不过有一部分还是要在程序里写的,能不能单纯用sql语句来实现呢?
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'sp_chkField' AND type = 'P') DROP PROCEDURE sp_chkField GO CREATE PROCEDURE sp_chkField(@unNeedField varchar(16),@tablename varchar(16)) AS Set Nocount On DECLARE @SQL VARCHAR(8000) SELECT @SQL='SELECT ' DECLARE @FieldStr VARCHAR(16) DECLARE tmpCur CURSOR LOCAL FOR SELECT [A].[NAME] FROM syscolumns A WHERE ID IN(SELECT ID FROM sysobjects WHERE NAME =@tablename) AND NAME !=@unNeedField OPEN tmpCur FETCH NEXT FROM tmpCur INTO @FieldStr WHILE @@FETCH_STATUS=0 BEGIN SELECT @SQL=@SQL + @FieldStr+ ',' FETCH NEXT FROM tmpCur INTO @FieldStr END SELECT @SQL=LEFT(@SQL,(SELECT LEN(@SQL)-1)) + ' FROM '+@tablename+'' close tmpCur deallocate tmpCur PRINT @SQL ---------exec (@sql) GO
--------单纯的语句,别忘了表名,列名哦---------------- DECLARE @SQL VARCHAR(8000) SELECT @SQL='SELECT ' DECLARE @FieldStr VARCHAR(16) GODECLARE tmpCur CURSOR LOCAL FOR SELECT [A].[NAME] FROM syscolumns A WHERE ID IN(SELECT ID FROM sysobjects WHERE NAME =@tablename) AND NAME !=@unNeedField OPEN tmpCur FETCH NEXT FROM tmpCur INTO @FieldStr WHILE @@FETCH_STATUS=0 BEGIN SELECT @SQL=@SQL + @FieldStr+ ',' FETCH NEXT FROM tmpCur INTO @FieldStr END close tmpCur deallocate tmpCurGO SELECT @SQL=LEFT(@SQL,(SELECT LEN(@SQL)-1)) + ' FROM '+@tablename+'' exec (@sql) GO
先把表复制一个,然后删除你那一列不要的,就可以得到你所要的 select * into #table from tablename alter table #t drop column filed1 select * from #t
得到一个你字段的集合,你组个串,然后配个SQL语句
循环配到,例如@str='field2,field3,....fieldn'
@SQL= 'select '+@str+' from A '
exec @sql
FROM sysobjects
WHERE name = N'sp_chkField'
AND type = 'P')
DROP PROCEDURE sp_chkField
GO
CREATE PROCEDURE sp_chkField(@unNeedField varchar(16),@tablename varchar(16))
AS
Set Nocount On
DECLARE @SQL VARCHAR(8000)
SELECT @SQL='SELECT '
DECLARE @FieldStr VARCHAR(16)
DECLARE tmpCur CURSOR LOCAL FOR
SELECT [A].[NAME] FROM syscolumns A WHERE ID IN(SELECT ID FROM sysobjects WHERE NAME =@tablename) AND NAME !=@unNeedField
OPEN tmpCur
FETCH NEXT FROM tmpCur INTO @FieldStr
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @SQL=@SQL + @FieldStr+ ','
FETCH NEXT FROM tmpCur INTO @FieldStr
END
SELECT @SQL=LEFT(@SQL,(SELECT LEN(@SQL)-1)) + ' FROM '+@tablename+''
close tmpCur
deallocate tmpCur
PRINT @SQL
---------exec (@sql)
GO
DECLARE @SQL VARCHAR(8000)
SELECT @SQL='SELECT '
DECLARE @FieldStr VARCHAR(16)
GODECLARE tmpCur CURSOR LOCAL FOR
SELECT [A].[NAME] FROM syscolumns A WHERE ID IN(SELECT ID FROM sysobjects WHERE NAME =@tablename) AND NAME !=@unNeedField
OPEN tmpCur
FETCH NEXT FROM tmpCur INTO @FieldStr
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @SQL=@SQL + @FieldStr+ ','
FETCH NEXT FROM tmpCur INTO @FieldStr
END
close tmpCur
deallocate tmpCurGO
SELECT @SQL=LEFT(@SQL,(SELECT LEN(@SQL)-1)) + ' FROM '+@tablename+''
exec (@sql)
GO
select * into #table from tablename
alter table #t drop column filed1
select * from #t