if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[p_qry]') and objectproperty(id, n'isprocedure') = 1) drop procedure [dbo].[p_qry] go
create proc p_qry @tbname sysname, --要查詢的表名 @keyfdname sysname=null --表中的主鍵,如果未指定,則表中無主鍵 as declare @nokey bit,@fd varchar(8000),@tj varchar(8000) set nocount on if isnull(@keyfdname,'')='' begin select @keyfdname=cast(newid() as char(36)),@nokey=1 exec('alter table ['[email protected]+'] add ['+[email protected]+'] decimal(38,0) identity(1,1)') end select @fd='',@tj='' select @[email protected]+',['+name+']' ,@[email protected]+'['+name+']=a.['+name+'] and ' from syscolumns where object_name(id)[email protected] and name<>@keyfdname set @fd=substring(@fd,2,8000) exec('select '[email protected]+' from ['[email protected]+'] a where exists(select 1 from ['[email protected] +'] where a.['[email protected]+'])'">'[email protected]+'['[email protected]+']<>a.['[email protected]+'])') if @nokey=1 exec('alter table ['[email protected]+'] drop column ['[email protected]+']') set nocount off go
--調用示例 --創建測試數據 create table 表(f1 int,f2 int,f3 int,f4 int,f5 int) insert into 表 select 1,1,1,1,1 union all select 2,1,1,1,1 union all select 3,2,1,23,1 union all select 4,2,3,1,3 union all select 5,1,1,1,1 go
