The Ultimate Object Searcher

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

--Create Util schema if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='Util') EXECUTE ('CREATE SCHEMA Util')
GO

IF OBJECT_ID('Util.Util_ObjectSearch', 'P') IS NOT NULL DROP PROCEDURE Util.Util_ObjectSearch
GO

/**
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_ObjectSearch
By Jesse Roberge - YeshuaAgapao@Yahoo.com

Searches for objects who's names contain the search string. outputs IDs, names, and types of the object and its parent.
LIKE wildcards are not automatic, which allows for easy 'begins with', 'ends with', and 'equal to' queries.

Required Input Parameters:
@ObjectName sysnameFilters tables.  Can use LIKE wildcards.  All tables if blank.

Optional Input Parameters:
@SchemaName sysname=''Filters schemas.  Can use LIKE wildcards.  All schemas if blank.
@UserOjbects TinyInt=0Searchthe names of user objects
@SystemObjects TinyInt=0Search the names of system objects
@AllTypes TinyInt=0Also search the names of constraints and old-style rules/defaults

Usage
EXECUTE Util_ObjectSearch '%mpi%'
EXECUTE Util_ObjectSearch @SchemaName='dbo', @ObjectName='%order%', @SystemObjects=1, @AllTypes=0

Copyright:
Licensed under the L-GPL - a weak copyleft license - you are permitted to use this as a component of a proprietary database and call this from proprietary software.
Copyleft lets you do anything you want except plagarize, conceal the source, proprietarize modifications, or prohibit copying & re-distribution of this script/proc.

This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as
    published by the Free Software Foundation, either version 3 of the
    License, or (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    see <http://www.fsf.org/licensing/licenses/lgpl.html> for the license text.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
**/
CREATE PROCEDURE Util.Util_ObjectSearch
@ObjectName sysname,
@SchemaName sysname='',
@UserObjects TinyInt=1,
@SystemObjects TinyInt=0,
@AllTypes TinyInt=0
AS

SELECT
objects.system_object,
objects.type, objects.type_desc,
schemas.schema_id, schemas.name AS schema_name,
objects.object_id, objects.name AS object_name,
objects_parent.type AS parent_type, objects_parent.type_desc AS parent_type_desc,
schemas_parent.schema_id AS parent_schema_id, schemas_parent.name AS parent_schema_name,
objects_parent.object_id AS parent_object_id, objects_parent.name AS parent_object_name
FROM
(
SELECT 0 AS system_object, object_id, name, schema_id, type, type_desc, parent_object_id
FROM sys.objects
WHERE
1=@UserObjects
AND (
@AllTypes=1
OR objects.type IN ('af','fn','fs','ft','if','p','pc','sn','ta','tf','tr','u','v','x')
)
AND objects.name LIKE @ObjectName
UNION ALL
SELECT 1 AS system_object, object_id, name, schema_id, type, type_desc, parent_object_id
FROM sys.system_objects AS objects
WHERE
1=@SystemObjects
AND (
@AllTypes=1
OR objects.type IN ('af','fn','fs','ft','if','p','pc','sn','ta','tf','tr','u','v','x')
)
AND objects.name LIKE @ObjectName
) AS objects
JOIN sys.schemas ON objects.schema_id=schemas.schema_id
LEFT OUTER JOIN sys.all_objects AS objects_parent ON objects.parent_object_id=objects_parent.object_id
LEFT OUTER JOIN sys.schemas AS schemas_parent ON objects_parent.schema_id=schemas_parent.schema_id
WHERE
schemas.name LIKE CASE WHEN @SchemaName='' THEN schemas.name ELSE @SchemaName END
ORDER BY objects.type, schemas.name, objects.name
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=