Friday, May 11, 2007

Object Search Window missing !!

One of my friend asked me is there a way to search for specific object (table,sp, trigger or column) based on key word. Immediately I said yes and tried to look for that search utility within the SQL 2005 Server Management Studio and tried pressing F4 also but nothing popped-up.

I was really surprise and woundering where the Object search window is. Out of frustration I look into Google, not for objects but to know where the Object search window is gone. Then came up with the anwers that Object search functionality is taken out of SQL 2005 tool set. After all this research decided to write my query to do the task, and here is the T-SQL query solution for the problem


SELECT name[ObjectName],
CASE xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'Inlined table-function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as ObjectType
FROM sysobjects WHERE NAME LIKE '%customer%'
SELECT object_name(id)+' >> '+name, 'column' FROM syscolumns WHERE NAME LIKE '%customer%'


Here is how the output looks like

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.