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


 



 

Delete duplicated rows in tables without Primary Key

Sometimes we worked in places that don’t have Primary keys in some tables, when we need to delete some duplicated row, it’s impossible to select only one row.

If you don’t have any possibilities for changing table specifications, you can use this script.
It deletes duplicated rows in tables without Primary Key, it creates a temporary Id for deleting rows.

Download the script:
Script delete rows without key