Lists tables, Stored Procedures and other Objects

Dima Pilugin, 2012-10-29 (first published: 2012-10-03)

This stored procedure helps you list names of all tables, stored procedure, function, view or a trigger based on the argument. Many a times, we don't remember the table name or the stored proc name. It could be the case with newbee's too. This stored procedures comes handy then. Just pass few characters of the table name, this stored procedure lists all possible table names with those characters. For example, you want to find all tables containing the characters "emp" in their names, then do the below.

exec sp_assist 'table', 'emp'

This should list all tables having "emp" in their names.

This stored procedure works for stored procedure, trigger, function, view.

Pass appropriate parameter as shown in trace and get listed the object type that you would want.

If you want to list all tables or stored procedures or function etc. then don't just pass any value to the second parameter.

Sample trace is there in the stored procedure comment section for help.

Note: This stored procedure can be expanded for other type of objects that you may want. I have just listed the basic ones.

Create this stored procedure in master database so that it is accessible to all other databases in that server.

create procedure sp_assist @object_type varchar(1000), @object_name varchar(1000) = null as begin /* trace get complete table having characters passed : exec sp_assist 'table', 'employee' get complete sp name having characters passed : exec sp_assist 'stored proc', 'employee' get complete fn name having characters passed : exec sp_assist 'function', 'employee' get complete sp name having characters passed : exec sp_assist 'trigger', 'trg' get complete sp name having characters passed : exec sp_assist 'view', 'employee' Lists all tables in that database : exec sp_assist 'table' Lists all stored proc in that database : exec sp_assist 'stored proc' Lists all functions in that databse : exec sp_assist 'function' Lists all triggers in that database : exec sp_assist 'trigger' Lists all views in that database : exec sp_assist 'view' */ set nocount on if @object_type = 'table' and @object_name is null select 'select * from ' + rtrim(name) from sys.objects where type = 'u' if @object_type = 'table' and @object_name is not null select 'select * from ' + rtrim(name) from sys.objects where type = 'u' and name like '%'+ rtrim(@object_name)+ '%' escape '.' if @object_type = 'stored proc' and @object_name is null select 'sp_helptext '+ rtrim(name) from sys.objects where type = 'p' if @object_type = 'stored proc' and @object_name is not null select 'sp_helptext '+ rtrim(name) from sys.objects where type = 'p' and name like '%'+ rtrim(@object_name)+ '%' escape '.' if @object_type = 'function' and @object_name is null select 'sp_helptext '+ rtrim(name) from sys.objects where type = 'fn' if @object_type = 'function' and @object_name is not null select 'sp_helptext '+ rtrim(name) from sys.objects where type = 'fn' and name like '%'+ rtrim(@object_name)+ '%' escape '.' if @object_type = 'trigger' and @object_name is null select 'sp_helptext '+ rtrim(name) from sys.objects where type = 'tr' if @object_type = 'trigger' and @object_name is not null select 'sp_helptext '+ rtrim(name) from sys.objects where type = 'tr' and name like '%'+ rtrim(@object_name)+ '%' escape '.' if @object_type = 'view' and @object_name is null select 'sp_helptext '+ rtrim(name) from sys.objects where type = 'v' if @object_type = 'view' and @object_name is not null select 'sp_helptext '+ rtrim(name) from sys.objects where type = 'v' and name like '%'+ rtrim(@object_name)+ '%' escape '.' end

Lists tables, Stored Procedures and other Objects

Rate

Share

Share

Rate