This post originated from an RSS feed registered with .NET Buzz by John Papa. Original Post: Find a Column, Find a Table in SQL Server Feed Title: John Papa Feed URL: /error.htm?aspxerrorpath=/blogs/john.papa/rss.aspx Feed Description: .NET Code Samples, Data Access, Patterns and Other Musings Latest .NET Buzz Posts Latest .NET Buzz Posts by John Papa Latest Posts From John Papa

OK, so this isn’t rocket science, but then again some of the best things are the simplest. You can do a lot with the information_schema views in SQL Server.

Here is a short stored procedure that you can use to find:

• a table by its full name
• a table by partial name
• a column by its full name
• a column by its partial name

 It comes in handy for me when I am looking for a field in related tables, or a spec I am reading refers to a field by name but not by the table it is in. Like I said, it is simple, but I have used this type of a proc for a long time.

CREATE PROCEDURE prFindTableOrColumn
(
	@table_name VARCHAR(128) = NULL,
	@search_data VARCHAR(128) = NULL
)
AS
	
	DECLARE @search1 VARCHAR(128)
	SET @search1 = '%' + @table_name + '%'
	IF @search1 IS NULL
		SET @search1 = '%'
	
	DECLARE @search2 VARCHAR(128)
	SET @search2 = '%' + @search_data + '%'
	IF @search2 IS NULL
		SET @search2 = '%'
	
	SELECT
		c.table_name,
		c.column_name,
		c.data_type,
		c.character_maximum_length,
		c.numeric_precision,
		c.numeric_scale
	FROM	
		information_schema.columns c
		INNER JOIN information_schema.tables t 
			ON c.table_name = t.table_name 
	WHERE
		c.table_name LIKE @search1
		AND c.column_name LIKE @search2
		AND t.table_type = 'BASE TABLE'
	ORDER BY 
		c.table_name,
		c.column_name