Some times we want to find a stored procedure but don’t remember exactly sp name then it’s bit complex to find the whole name of the sp in Management Studio. But might be you know that SQL Server provides some system object from there we can easily finds the any object name. There are inbuilt tables which stores the all the details of table, view, sp, triggers, comment etc….
Like there is one table name sys.procedure which has all the information about available stored procedures.
If we want to find all the sp which starts name ‘adm’ then just write below qyery
select * from sys.procedures where name like 'adm%'
Like SP we can also find details about all the available tables in SQL Server.
select * from sys.tables where name like 'adm%'
above query will returns all the tables which starts with adm so we can use like clause as we are using in normal table.