sp_help with wildcards

Question

There is a command in SqlServer sp_help 'table_name' (table_name or other stuff)

Is there a possibility to use it when you don't know the whole table name, with wildcards or something like this:

sp_help admin_%

EDIT:

Run sp_help (SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME like '%admin_%') but had a strange result...

Answer 1

No, SP_HELP requires a complete name, so use this to find the table name first:

SELECT
    *
    FROM INFORMATION_SCHEMA.Tables
    WHERE TABLE_NAME like '%your table name guess here%'

then you can use sp_help 'the_real_complete_name'

EDIT based on OP's comment

You don't need to remember the query, just create a procedure to search for object names:

CREATE PROCEDURE [FO] 
    @SearchFor sysname=''
AS

SELECT
    name,type_desc
    FROM sys.objects 
    WHERE name like '%'+@SearchFor+'%'
    ORDER BY type,name
GO

now just use it like:

fo 'xyz'

or

exec fo 'xyz'

to find all of the objects with a matching name. These are the types of objects that can be found:

AGGREGATE_FUNCTION
CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
FOREIGN_KEY_CONSTRAINT
PRIMARY_KEY_CONSTRAINT
SQL_STORED_PROCEDURE
CLR_STORED_PROCEDURE
SQL_SCALAR_FUNCTION
CLR_SCALAR_FUNCTION
CLR_TABLE_VALUED_FUNCTION
RULE
REPLICATION_FILTER_PROCEDURE
SYNONYM
SERVICE_QUEUE
CLR_TRIGGER
SQL_TRIGGER
SQL_INLINE_TABLE_VALUED_FUNCTION
SQL_TABLE_VALUED_FUNCTION
USER_TABLE
UNIQUE_CONSTRAINT
VIEW
EXTENDED_STORED_PROCEDURE
INTERNAL_TABLE

This is off the main topic, but you can also search for any text within a stored procedure, trigger, view, or function using this:

CREATE PROCEDURE [FT]
    @Search varchar(255)
AS

SELECT DISTINCT
    o.name,o.type_desc
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY type_desc

GO

just use it like:

ft 'fkgjfg'

or

exec ft 'fkgjfg'

Answer 2

It doesn't look like it's meant to be used this way. I bet someone here will come up with another way around it though!

Answer 3

The following query will give you a list of tables that match the name you want.

select * from INFORMATION_SCHEMA.Tables 
where TABLE_NAME like 'admin%'

You can then execute sp_help on the resulting table name.

Answer 4

If your trying to implement something special with the functionality of sp_help you can create your own version by copying its source. (sp_helptext sp_help)