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') - contains 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 - to check whether to include Text, NText, Image data type SET @INCLUDE_NTI = UPPER (LTRIM (RTRIM (@INCLUDE_NTI))) iF @INCLUDE_NTI Not in ('n', 'y') set @include_nti = 'n' --construct a curusor to get the list of table / column names accounting to the @tablename and @columnname parameters. - Create a cursor to read A list of table names and column names, where the list is determined by the parameter @Tablename and @Columnname set @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 sol = su.uID WHERE SO.XTYPE = '' u '' - Filter Out Text / NTEXT / Image DATA TYPES IT / NTEXT / Image DATA TYPES if IT IT / Image Does not include the text / ntext / image data type, filter out the IF @include_nti = 'N '- ISCOLUMN 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 Supplied - If you have a form name, write it into filter criteria IF @tablename is not null and ltrim (@@ @@
TableName)) <> 'begin set @tablename = replace (@tablename,', ',', ') set @strsql = @strsql ' and (So.name like '' replace (@tablename, ', ',' 'Or sol.name like' ') ' '') 'set @ SQLTEMP =' AND (So.name Like '' Replace (@tablename, ',', '' OR SO. Name Like '' ') ' ')' end --add the column (s) Name IE filter if it is supplished - if there is a collection of column name parameters, 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 DECLATION" String - Execute Cursor's SQL statement 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 -. explicit marking a starting point of local transaction BEGIN tRANSACTION gDatabaseTrans --Open THE CURSOR - Open Cursor Open Tabcolcursor - Fetch Te Table, Column Names To Vari Aables - Take the name with the cursor, column name, the column name, the parameter fetch next from tabcolcursor @Stablename, @scolumnname --Execute the SQL Statement Supplied in @SQL Parameter On Every Row of Cursor's Data - Data taken for each row , Execute SQL statement that is passed from @SQL parameters while @@ fetch_status = 0 begin --Construct Sql2 to execute support @sql --by replacing @
TableName, @columnname with running table name, column name of cursor's data - Replace @Tablename, @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 - error, destroy objects, rollback transaction --return -1 as unsuccessful flag - If an error occurs, delete a cursor, rollback - return error tag -1 print 'error curred' DEALLOCATE TabColCursor ROLLBACK TRANSACTION gDatabaseTrans RETURN -1 END --Process next row of cursor - the next row of data FETCH nEXT FROM TabColCursor INTO @ 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, successfully ended the transaction - Return success mark 0 Commit Transaction GDATABASETRANS RETURN 0End use Example 1, this example is performed on the Northwind database to include all tables In the column of name, the column ends with "LTD." is replaced with "Limi" TED ".
