Posts

Showing posts from June, 2015

TSQL - Search Column and its Value from ALL tables in database

Hi Guys, Sometimes we try to find a specific Column Name from all the tables in the database. Below is the query-   SELECT t . name AS table_name , SCHEMA_NAME ( schema_id ) AS schema_name , c . name AS column_name FROM sys . tables AS t INNER JOIN sys . columns c ON t . OBJECT_ID = c . OBJECT_ID WHERE c . name like '%Column_Name%' ORDER BY schema_name , table_name Of course everyone knows about it, you can google it. But below is the query to find a   Column Name and its Value  from all the tables in database. Isn’t interesting !! CREATE PROC SearchColumnAndItsValueFromAllTables  (       @SearchStr nvarchar ( 100 )   )   AS   BEGIN         CREATE TABLE #Results ( ColumnName nvarchar ( 370 ), ColumnValue nvarchar ( 3630 ))         SET NOCOUNT ON         DECLARE @TableName nvarchar ( 256 ), @ColumnName nvarchar ( 128 ), @SearchStr2 nvarchar ( 110 )       SET   @TableName = ''