SQL Server 2005 - missing query tool for tablenames - a solution

It pissed me off, BIG time... Having installed a less-than-perfect first official release of SQL Server 2005 client tools. It didn't even have intellisense on sql queries ! Furthermore the functionlality of good old Query Analyzer where you could search for database objects was not included.

As a workaround, I have made this simple stored procedure that will do a search on any object in the database, containing a part of the input string in its name. Simply put the stored procedure in any database you might want to query on database objects on a regular basis.


create procedure st
@Sstring varchar(50)
as
SELECT
name,
CASE
WHEN xtype = 'U' then 'table'
WHEN xtype = 'P' then 'Stored Procedure'
END as type
FROM
sysobjects
WHERE
xtype in ('U','P')
AND name like '%' + @Sstring + '%'
Order By name
GO


xtype='U' are all user tables.
xtype = 'P' are all stored procedures.

Have fun !