The Ultimate Object Searcher

,

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
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 sysname Filters 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=0 Searchthe names of user objects
@SystemObjects TinyInt=0 Search the names of system objects
@AllTypes TinyInt=0 Also 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.

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

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 sysname			Filters 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=0		Searchthe names of user objects
	@SystemObjects TinyInt=0	Search the names of system objects
	@AllTypes TinyInt=0			Also 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

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

Rate

Share

Share

Rate