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
/ * - The universal stored process of query repeated records can query those data in the table are repeated, and the repetition here is that the records except the primary key are repeated. If there is a primary key, please specify the primary key. If there is a logo field And the identification field is not repeated, please specify the primary key when calling, if the identity field is repeated, this stored procedure cannot be used
- 2004.4 - * /
CREATE PROC P_QRY @ tbname sysname, - To query the table name @keyfdname sysname = null - the primary key in the table, if not specified, the table does not have the primary key asDeclare @nokey bit, @ fd varchar (8000), @ TJ varchar (8000) SET NOCOUNT ONIF ISNULL (@keyfdname, '') = '' begin select @ keyfdName = Cast (newid () as char (36)), @ nokey = 1 Exec ('alter table [' @ TBNAME '] Add [' @ KeyfDName '] decimal (38, 0) Identity (1, 1) ') endselect @fd =' ', @ TJ =' 'select @ fd = @ fd ', [' Name '] ', @ TJ = @ TJ '[' Name '] = a. [' Name '] and' from syscolumns where object_name (id) = @ kyfdnameset @ fd = Substring (@ fd, 2,8000 ) EXEC ('SELECT' @ fd 'from [' @ TBNAME '] a where exists (SELECT 1 from [' @ TBNAME '] WHERE' @ TJ '[' @ KeyfDName '] <> a. [' @ KeyfDName ']) ') IF @ NOKEY = 1 EXEC (' alter table [' @ TBNAME DROP Column [' @ KeyfDName ']) Set NoCount Off Go
- Call the sample - Create a test data CREATE TABLE Table (F1 INT, F2 INT, F3 INT, F4 INT, F5 INT) INSERT INTO Table Select 1, 1, 1, 1 UNION All SELECT 2, 1, 1, 1 , 1Union All SELECT 3, 2, 1, 23, 1 UNION All SELECT 4, 2, 3, 1, 3UNION All SELECT 5, 1, 1, 1, 1GO
- Call the universal stored procedure to realize the landlord Exec P_QRY 'Table', 'F1' - Delete Test Environment DROP TABLE Table
/*--Test Results
F2 F3 F4 F5 ----------------------------------------- 1 1 1 11 1 1 11 1 1 1 - * /
