Why object explorer search does not return any results when I enter a column name?
-
I do know how to find an column in database ... 🙂
This just drives me 'crazy'....
I've searched the inet....nothing...no info
Someone please!!!! explain!!!!!
:-P:-P:-P
MS for sql server 2008 R2 SP2
-
I'm not going to explain why something doesn't work, but i will show you how i handle it.
SSMS has the ability to call keyboard shortcuts, which call a procedure of your choice. i have a suite of them for doing everyday things when i'm developing.
in my case, I created a proc called sp_find, stuck it in master and marked it as a system proc.
It takes a parameter and searches for any table/view or column name that contains the string...
so if i KNOW there's some columns that contain part of the word "readonly" for example, i highlight a portion of the text, and hit CONTROL + 7
I get results like you see here, and here's a screenshot of my shortcuts.
my proc, which you can see is pretty simple:
----EXECUTE sp_ms_marksystemobject 'sp_find'
CREATE PROCEDURE [dbo].[sp_find]
@findstring varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT
TableFound,
ColumnFound,
ObjectType
FROM
(
SELECT
1 AS SortOrder,
sys.objects.name AS TableFound,
'' AS ColumnFound,
sys.objects.type_desc As ObjectType
FROM sys.objects
WHERE sys.objects.type_desc
IN('USER_TABLE',
'VIEW',
'SQL_SCALAR_FUNCTION',
'CLR_SCALAR_FUNCTION',
'CLR_TABLE_VALUED_FUNCTION',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'SQL_STORED_PROCEDURE',
'CLR_STORED_PROCEDURE',
'SQL_TABLE_VALUED_FUNCTION')
AND sys.objects.name LIKE '%' + @findstring + '%'
UNION ALL
SELECT
2 AS SortOrder,
sys.objects.name AS TableFound,
sys.columns.name AS ColumnFound,
'COLUMN_NAME' As ObjectType
FROM sys.objects
INNER JOIN sys.columns ON sys.objects.object_id=sys.columns.object_id
WHERE sys.objects.type_desc IN('USER_TABLE' ,'VIEW')
AND sys.columns.name like '%' + @findstring + '%'
) X
ORDER BY
SortOrder,
TableFound,
ColumnFound,
ObjectType
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Great solution, very convenient. Thank you for sharing it!!!
I am using sys objects catalogs\views as well as free redgate tool ‘SQL Search’….
It just bugging me WHY it is NOT showing in a native sql GUI.
Isn’t column name would be in the top of the objects list that dba looking for and would like it to be ‘Right There’ at his finger tips?
If someone can save my sanity….i would really appreciate it .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply