Search text values in All Tables and the SPs in same database This stored procedure/SQL Statement developed/tested in SQL Server 2008 and it has all backward compatability. · Search text value in All the Stored Procedure in same database Following statement will search text value in all the SP created in the same database. SELECT * FROM SYSCOMMENTS WHERE TEXT LIKE'%<search Value>%' · Search text values in All tables in same database This SP will search text string in all the database tables in all the columns. Ex: If you want to search one text like 'Country name: USA' then you run following SP and execute like Exec SearchInAllTables 'Country name: USA' You will get all the table name and related column name where this text exists. USE [Database name] GO CREATE PROCEDURE SearchInAllTables ( @searchString NVARCHAR(1000) ) AS BEGIN CREATE TABLE #OutputResults (ColumnNa NVARCHAR(500), ColumnVal NVARCHAR(4000)) SET NOCOUNT ON DECLARE @tableNa NVARCHAR(300), @columnNa NVARCHAR(200), @searchString2 NVARCHAR(1000) SET @tableNa = '' SET @searchString2 = QUOTENAME('%' + @searchString + '%','''')
WHILE @tableNa IS NOT NULL SET @columnNa = '' SET @tableNa = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @tableNa AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@tableNa IS NOT NULL) AND (@columnNa IS NOT NULL) BEGIN SET @columnNa = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@tableNa, 2) AND TABLE_NAME = PARSENAME(@tableNa, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @columnNa ) IF @columnNa IS NOT NULL BEGIN INSERT INTO #OutputResults EXEC ( 'SELECT ''' + @tableNa + '.' + @columnNa + ''', LEFT(' + @columnNa + ', 3630) FROM ' + @tableNa + ' (NOLOCK) ' + ' WHERE ' + @columnNa + ' LIKE ' + @searchString2 ) END END END SELECT ColumnNa, ColumnVal FROM #OutputResults END GO |
Tuesday, July 20, 2010
Search text values in All Tables and the SPs in same database
Tuesday, July 13, 2010
Error: Unrecognized attribute 'targetFramework'. (Window 7)
Error: Unrecognized attribute 'targetFramework'. I am using visual studio 2010, IIS 6.1 and OS is window 7, when I create web service using visual studio 2010 and run I am getting this error, after couple of research I found that this error causing because of web service created for .NET Framework 4.0 but IIS is pointing it to .NET Framework 2.0. You can do the following steps to point your application into .NET framework 4.0 1) Open IIS (type inetmgr on Run window) 2) Go to "Application Pool" 3) Select application folder and double click 4) On the "Edit Application Pool" popup change .NET Framework 2 to 4. 5) Restart IIS |
Thursday, July 08, 2010
“Manage Content and Structure” is not visible on Share Point “Site Actions”
"Manage Content and Structure" is not visible on Share Point "Site Actions" 1) Go to "Site Action" 2) Click on "Site Settings" 3) On the "Site Actions" section go to 4) Click on "Manage site features" and 5) Activate "SharePoint Server Publishing" Now go to the "Site Action" again you will see one more option "Manage Content and Structure" Note: If you get Runtime error while clicking on "Manage Content and Structure" then do the following steps 1) Click on "Site Actions" 2) Go to "Site Settings" 3) Go to the "Site Collection Administration" 4) Click on "Site Collection features" 5) Activate "SharePoint Server Publishing Infrastructure" Now again click on "Manage Content and Structure" you will not get any runtime error. |