lunes, 1 de febrero de 2010

Stored Procedure Find Text

This script allows to find any text in column names, views, table names or inside stored procedures.



create PROCEDURE [dbo].[NCO_ADMIN_FindText] @Texto varchar(200)


AS


BEGIN


--Para que aparezcan todos los registros poner '' o null




SET NOCOUNT ON;




if @Texto='' or @Texto is null set @Texto='%'


----Comentarios


SELECT DISTINCT O.NAME, 'SP' as tipo


FROM SYSOBJECTS O


INNER JOIN SYSCOMMENTS C ON O.ID=C.ID


WHERE TEXT LIKE '%' + @Texto + '%'


and O.uid=1 and xtype='P'


union all


--Tablas


SELECT distinct o.name, 'TABLA' as tipo


FROM SYSOBJECTS o 


inner join sys.tables tab on o.id=tab.object_id


WHERE tab.name LIKE '%' + @Texto + '%'


and O.uid=1 and xtype='U'


union all


--Vistas


SELECT distinct o.name, 'VISTA' as tipo


FROM SYSOBJECTS o 


inner join sys.all_views vw on o.id=vw.object_id


WHERE vw.name LIKE '%' + @Texto + '%'


and O.uid=1 and xtype='V'


union all


--Columnas


SELECT distinct o.name+'.'+col.name, 'Columna' as tipo


FROM SYSOBJECTS o 


inner join sys.all_columns col on o.id=col.object_id


WHERE col.name LIKE '%' + @Texto + '%'


and O.uid=1


ORDER BY O.NAME,2




RETURN @@ROWCOUNT


END


 



 

No hay comentarios:

Publicar un comentario