How to simultaneously operate multiple tables or column operations
By using this stored procedure, you can easily have a certain rule or all tables in the database, and see the fields here, specifically see example!
Create Procedure Sp_execsqlondb (@tablename VARCHAR (50), - Table Name Condition @ColumnName Varchar (50), - Field Condition @SQL NVARCHAR (4000), SQL @Include_nti char (1) = 'n') - Whether to include Text, NText, Image Data Type as Begin - Variable Declaration - Variable Definition Declare @strsql Nvarchar (4000) Declare @ SQL2 NVARCHAR (4000) Declare @stablename Varchar (200) Declare @scolumnname varchar (200)
Declare @sqltemp nvarchar (4000)
--Check WHETHER TO INCLUDE TEXT, NTEXT, Image DATA TYPES - Check if you need to include text, ntext, image data type set @include_nti = Upper (Ltrim (rinClude_nti))) if @include_nti not in ('n' , 'Y') set @include_nti = 'n'
--Construct a cursor to get the list of top @tablename and @columnname parameters. - Create a cursor to read a list of table names and column names, here the list is determined by the parameter @tablename and @Columnname @STRSQL = N'Declare TabColcursor Cursor for Select RTRIM (Ltrim (SU.NAME)) ''. '' Ltrim (Rtrim (So.name)), sc.Name from sysobjects so inner join syscolumns sc on sol = Sc.id inner Join Sysusers Su On So.uid = Su.uid WHERE SOTYPE = '' U '' '
- Filter Out Text / Ntext / Image Data Types if IT IS Not Included - If you do not include the text / ntext / image data type, filter out the IF @include_nti = 'n' - Xin Syscolumns Sytem Table Xtype Column Corresponds To Column data type set @strsql = @strsql 'and sc.XTYPE NOT IN (35, 99, 34)'
--Add the table (s) Name IE filter if it is supplished - If there is a form name, write it into filter conditions if @tablename is not null and ltrim (@tablename) <> '' Begin set @tablename = replace (@tablename, ',', ',') set @strsql = @strsql 'and (So.name like' ' replace (@tablename,', ',' 'OR SO .name like '' ') ' ')' set @ Sqltemp = 'and (So.name Like' '' Replace (@tablename, ',', '' or soli be '') '') 'End - add the column (s) Name IE filter if it is support - If you have a column name parameter, write it into filter conditions if @columnname is not null and ltrim (@columnname )) <> 'Begin set @columnname = replace (@columnname,', ',') set @strsql = @strsql 'and (sc.name like' ' replace (@columnname,', ' , '' Or sc.name like '') '') 'end
--Execute the constructed "Cursor Declaration" string-- execute SQL statement defines the cursor EXECUTE sp_executesql @strSQL IF @@ ERROR> 0 BEGIN PRINT 'Error while declaring the Cursor. Please check out the parameters supplied to the Procedure' RETURN -1 End
- Database Transaction.
- Open THE CURSOR - Open Cursor Open TabColcursor
- FETCH TE TETABLE, Column Names To Variables - Take the name with a cursor, column name, corresponding to parameter fetch next from tabcolcursor @stablename, @scolumnname
--Execute the sql statement support in @sql parameter on every row of cursor's data - Data taken by the cursor, execute the SQL statement that is passed from @SQL parameters while @@ fetch_status = 0 begin --Construct SQL2 To Execute Supplied @SQL --BY Replacing @tables, @columnname with running table name, column name of cursor's data - Replace @Tablename in @SQL with cursor replace @columnname to construct SQL2 set @ SQL2 = @SQL SET @ SQL2 = REPLACE (@ SQL2, '@TABLENAME', @sTableName) SET @ SQL2 = REPLACE (@ SQL2, '@COLUMNNAME', @sColumnName) - execute the constructed SQL2 - performing SQL2 eXECUTE sp_executesql @ SQL2 --Check for errors - Check Error IF @@ Error <> 0 Begin --on Error, Destroy Objects, Rollback Transaction - Return -1 AS Unsuccessful Flag - If an error occurs, delete a cursor, rollback - return an error Tag -1 Print 'error Occurred' deallocate TabColcursor Rollback Transaction GDATABASETRANS RETURN -1 END
--Process Next Row of Cursor - Make the next line of data Fetch next from tabcolcursor @ stablename, @ Scolumnname End
--DESTROY CURSOR OBJECT - Delete Cursor Deallocate TabColcursor
--Procedure Executed Properly. Commit The Transaction. - Return 0 As Successful Flag - Successfully completed the stored procedure, successful end of the transaction - Return Success Tag 0 Commit Transaction GDatabaseTrans Return 0nd
Use example
1. This example is executed in the column containing the names in all tables on the Northwind database, replacing the column ending with "LTD." into "Limited". Use SELECT * from Supplier to check the run results!
EXEC SP_EXECSQLONDB ', - No table name, for all table'% name% ', - column name condition, column name contain "name" string' update @tablename set @columnname = replace (@columnname, '' LTD. '', '' '' ') Where @Columnname Like' '% Ltd.' '', --Update Statement 'N' - does not include NTEXT, Text, Image Data Type 2, this example is also in the Northwind database The value of the column containing Name in all tables is "Quick-Stop" quantity Create Table ## TMP1 (Table_Name Varchar (200), Column_Name Varchar (200), Rou_Count Int) EXEC SP_EXECSQLONDB '', '% Name% ',' declare @icount as int search @ iCount = count (1) from @tablename where @columnname = '' Quick-stop '' ing @icount> 0 Insert INTO ## TMP1 Select '' @tablename '', '' @Columnname '', @ iCount ',' N'Select * from ## TMP1
3, this example you understand that the stored procedure is performed with the field that starts with "EMPLOYEE" to perform the stored procedure with the field starting with "DEPT". EXEC SP_EXECSQLONDB'EMPLOYEE% ',' de PEC% ',' exec usp_deptstates' '@tablename' ',' '@ columnname' '', 'N'
4, or understand the @TableName @ColumnName parameters give more values! Exec sp_execsqlondb'employee%, PF% ',' Salary,% Amount% ',' EXEC USP_EMPLOYEE_PF ',' N '
