How to find a column in database (SQL Server)

find_keywords
Want to know where that specific column is located in your database?
Or just want to figure out where that data is stored?





You’re able to locate a specific column by using this query:

SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%keyword%'
ORDER BY SchemaName, ColumnName



Since I didn’t want to type it again and again…I made a Stored Procedure (SP) for it:

CREATE PROCEDURE [dbo].[usp_FindColumnInAllTables]
(
@KeyWord NVARCHAR(200)
)
AS

BEGIN

SELECT
t.name AS TableName,
SCHEMA_NAME(schema_id) AS SchemaName,
c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%'+@KeyWord+'%'
ORDER BY SchemaName, ColumnName

END



Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s