New a query that will search database for all tables that have a certain column name in common.

I need a query that will search all of the tables in my database that have a common column name. For example if I have a column named ClientId, I need to find all data tables in the database that have a column named "ClientId". Does anyone know of a query that would do that?
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
Try creating and using the following stored procedure.
Reference TechNet: https://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58

IF OBJECT_ID('SP_SearchTables','P') IS NOT NULL
	DROP PROCEDURE SP_SearchTables
GO
CREATE PROCEDURE SP_SearchTables
 @Tablenames VARCHAR(500)
,@SearchStr NVARCHAR(60)
,@GenerateSQLOnly Bit = 0
AS

/*
	Parameters and usage

	@Tablenames		-- Provide a single table name or multiple table name with comma seperated. 
						If left blank , it will check for all the tables in the database
						Provide wild card tables names with comma seperated
						EX :'%tbl%,Dim%' -- This will search the table having names comtains "tbl" and starts with "Dim"
							
	@SearchStr		-- Provide the search string. Use the '%' to coin the search. Also can provide multiple search with comma seperated
						EX : X%--- will give data staring with X
							 %X--- will give data ending with X
							 %X%--- will give data containig  X
							 %X%,Y%--- will give data containig  X or starting with Y
							 %X%,%,,% -- Use a double comma to search comma in the data
	@GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database. 
						By default it is 0 and it will search.

	Samples :

	1. To search data in a table

		EXEC SP_SearchTables @Tablenames = 'T1'
						 ,@SearchStr  = '%TEST%'

		The above sample searches in table T1 with string containing TEST.

	2. To search in a multiple table

		EXEC SP_SearchTables @Tablenames = 'T2'
						 ,@SearchStr  = '%TEST%'

		The above sample searches in tables T1 & T2 with string containing TEST.
	
	3. To search in a all table

		EXEC SP_SearchTables @Tablenames = '%'
						 ,@SearchStr  = '%TEST%'

		The above sample searches in all table with string containing TEST.

	4. Generate the SQL for the Select statements

		EXEC SP_SearchTables @Tablenames		= 'T1'
						 ,@SearchStr		= '%TEST%'
						 ,@GenerateSQLOnly	= 1

	5. To Search in tables with specfic name

		EXEC SP_SearchTables @Tablenames		= '%T1%'
						 ,@SearchStr		= '%TEST%'
						 ,@GenerateSQLOnly	= 0

	6. To Search in multiple tables with specfic names

		EXEC SP_SearchTables @Tablenames		= '%T1%,Dim%'
						 ,@SearchStr		= '%TEST%'
						 ,@GenerateSQLOnly	= 0
	
	7. To specify multiple search strings

		EXEC SP_SearchTables @Tablenames		= '%T1%,Dim%'
						 ,@SearchStr		= '%TEST%,TEST1%,%TEST2'
						 ,@GenerateSQLOnly	= 0


	8. To search comma itself in the tables use double comma ",,"

		EXEC SP_SearchTables @Tablenames		= '%T1%,Dim%'
						 ,@SearchStr		= '%,,%'
						 ,@GenerateSQLOnly	= 0

		EXEC SP_SearchTables @Tablenames		= '%T1%,Dim%'
						 ,@SearchStr		= '%with,,comma%'
						 ,@GenerateSQLOnly	= 0
*/

	SET NOCOUNT ON

	DECLARE @MatchFound BIT

	SELECT @MatchFound = 0

	DECLARE @CheckTableNames Table
	(
	Tablename sysname
	)

	DECLARE @SearchStringTbl TABLE
	(
	SearchString VARCHAR(500)
	)

	DECLARE @SQLTbl TABLE
	(
	 Tablename		SYSNAME
	,WHEREClause    VARCHAR(MAX)
	,SQLStatement   VARCHAR(MAX)
	,Execstatus		BIT 
	)

	DECLARE @SQL VARCHAR(MAX)
	DECLARE @TblSQL VARCHAR(MAX)
	DECLARE @tmpTblname sysname
	DECLARE @ErrMsg VARCHAR(100)

	IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
	BEGIN

		INSERT INTO @CheckTableNames
		SELECT Name
		  FROM sys.tables
	END
	ELSE
	BEGIN

		IF CHARINDEX(',',@Tablenames) > 0 
			SELECT @SQL = 'SELECT ''' + REPLACE(@Tablenames,',','''as TblName UNION SELECT ''') + ''''
		ELSE
			SELECT @SQL = 'SELECT ''' + @Tablenames + ''' as TblName '

		SELECT @TblSQL = 'SELECT T.NAME
							FROM SYS.TABLES T
							JOIN (' + @SQL + ') tblsrc
							 ON T.name LIKE tblsrc.tblname '


		

		INSERT INTO @CheckTableNames
		EXEC(@TblSQL)

	END
	
	IF NOT EXISTS(SELECT 1 FROM @CheckTableNames)
	BEGIN
		
		SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter'
		PRINT @ErrMsg
		RETURN

	END


	IF LTRIM(RTRIM(@SearchStr)) =''
	BEGIN

		SELECT @ErrMsg = 'Please specify the search string in @SearchStr Parameter'
		PRINT @ErrMsg
		RETURN
	END
	ELSE
	BEGIN 
		SELECT @SearchStr = REPLACE(@SearchStr,',,,',',#DOUBLECOMMA#')
		SELECT @SearchStr = REPLACE(@SearchStr,',,','#DOUBLECOMMA#')

		SELECT @SQL = 'SELECT ''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + ''''

		INSERT INTO @SearchStringTbl
		(SearchString)
		EXEC(@SQL)

		UPDATE @SearchStringTbl
		   SET SearchString = REPLACE(SearchString ,'#DOUBLECOMMA#',',')
	END

	
	
	INSERT INTO @SQLTbl
	( Tablename,WHEREClause)
	SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME),
			(
				SELECT '[' + SC.Name + ']' + ' LIKE ''' + SearchSTR.SearchString + ''' OR ' + CHAR(10)
				  FROM SYS.columns SC
				  JOIN SYS.types STy
					ON STy.system_type_id = SC.system_type_id
				   AND STy.user_type_id =SC.user_type_id
				  CROSS JOIN @SearchStringTbl SearchSTR
				 WHERE STY.name in ('varchar','char','nvarchar','nchar','text')
				   AND SC.object_id = ST.object_id
				 ORDER BY SC.name
				FOR XML PATH('')
			)
	  FROM  SYS.tables ST
	  JOIN @CheckTableNames chktbls
				ON chktbls.Tablename = ST.name 
	  JOIN SYS.schemas SCh
	    ON ST.schema_id = SCh.schema_id
	 WHERE ST.name <> 'SearchTMP'
      GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' +  QUOTENAME(ST.NAME) ;
	

	  UPDATE @SQLTbl
		 SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)


		 
	  DELETE FROM @SQLTbl
	   WHERE WHEREClause IS NULL
	
	WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
	BEGIN

		SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement
		  FROM @SQLTbl 
		 WHERE ISNULL(Execstatus ,0) = 0

		 IF @GenerateSQLOnly = 0
		 BEGIN

			IF OBJECT_ID('SearchTMP','U') IS NOT NULL
				DROP TABLE SearchTMP
				
			EXEC (@SQL)

			IF EXISTS(SELECT 1 FROM SearchTMP)
			BEGIN
				SELECT Tablename=@tmpTblname,* FROM SearchTMP
				SELECT @MatchFound = 1
			END

		 END
		 ELSE
		 BEGIN
			 PRINT REPLICATE('-',100)
			 PRINT @tmpTblname
			 PRINT REPLICATE('-',100)
			 PRINT replace(@SQL,'INTO SearchTMP','')
		 END

		 UPDATE @SQLTbl
		    SET Execstatus = 1
		  WHERE Tablename = @tmpTblname

	END

	IF @MatchFound = 0 
	BEGIN
		SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter'
		PRINT @ErrMsg
		RETURN
	END
	
	SET NOCOUNT OFF

go

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I need to find all data tables in the database that have a column named "ClientId".
Give this a whirl..
USE your_database_name
GO

SELECT t.name as table_name, c.name as column_name
FROM sys.tables t
   JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'ClientId'
ORDER BY t.name, c.name

Open in new window

0
brgdotnetcontractorAuthor Commented:
Jim, your code rocks. Thanks.
0
LajuanTaylorCommented:
@Jim Horn - Short and concise... I like your solution as well.

Regards
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.