IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_TRIGGERS]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_TRIGGERS]
GO
GO
 
--------------------------------------------------------------------------
--Objetivo: Procurar um texto dentro de triggers
-- by Ramam Freitas

CREATE PROCEDURE P_TRIGGERS (@DS_TEXTO AS VARCHAR(200))
AS

SELECT 
	OBJECT_NAME(C.ID) NM_TRIGGER,
	O.NAME TABELA,
	C.[TEXT] TEXTO
FROM 
	SYSCOMMENTS  C
	INNER JOIN SYS.TRIGGERS  T ON 
	C.ID = T.OBJECT_ID
	INNER JOIN SYSOBJECTS O ON 
	T.PARENT_ID = O.ID
WHERE 
	C.[TEXT] LIKE '%'+ @DS_TEXTO + '%' 
	AND OBJECTPROPERTY(C.id, 'IsTrigger') = 1 
  
go
IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_COLUNAS]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_COLUNAS]
GO
GO
--------------------------------------------------------------------------
--Objetivo: Retornar as colunas de uma tabela
--by Ramam Freitas  
  
CREATE PROCEDURE [dbo].[P_COLUNAS](@TABELA VARCHAR (50), @TP AS INT = 0 ) 
AS  
DECLARE @RS_RETORNO TABLE  
 (  
  TABELA VARCHAR (500),   
  COLUNA VARCHAR (500),   
  IS_NULL VARCHAR (50),   
  TIPO VARCHAR(150),   
  LENGTH VARCHAR (50))  
  
	IF @TP=0  
	BEGIN  
		INSERT INTO @RS_RETORNO  
		SELECT   
			TABLE_NAME,  
			COLUMN_NAME,   
			IS_NULLABLE,   
			DATA_TYPE,   
			CHARACTER_MAXIMUM_LENGTH   
		FROM  
			INFORMATION_SCHEMA.COLUMNS  
		WHERE  
			TABLE_NAME LIKE '' + @TABELA + ''  
		ORDER BY COLUMN_NAME  
	END  
	
	IF @TP=1  
	BEGIN  
		INSERT INTO @RS_RETORNO  
		SELECT   
			TABLE_NAME,  
			COLUMN_NAME,   
			IS_NULLABLE,   
			DATA_TYPE,   
			CHARACTER_MAXIMUM_LENGTH   
		FROM  
			INFORMATION_SCHEMA.COLUMNS  
		WHERE  
			TABLE_NAME LIKE '' + @TABELA + ''   
	END   

	IF @TP=2
	BEGIN  
		INSERT INTO @RS_RETORNO  
		SELECT   
			TABLE_NAME,  
			COLUMN_NAME,   
			IS_NULLABLE,   
			DATA_TYPE,   
			CHARACTER_MAXIMUM_LENGTH   
		FROM  
			INFORMATION_SCHEMA.COLUMNS  
		WHERE  
			COLUMN_NAME LIKE '' + @TABELA + ''   
	END   

	SELECT * FROM @RS_RETORNO  

 
go

IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[P_TABELAS]') AND TYPE IN (N'P', N'PC'))
DROP PROCEDURE [DBO].[P_TABELAS]
GO
GO
 
--------------------------------------------------------------------------
--Objetivo: Retornar tabelas com um nome especifico
--by Ramam Freitas  
CREATE PROCEDURE [dbo].[P_TABELAS](@TABELA VARCHAR (500))   
  
AS  
	DECLARE @RS_RETORNO TABLE  ( BANCO  VARCHAR (50),   
								NOME  VARCHAR(500),   
								SQL_NOME VARCHAR(MAX),   
								TIPO  VARCHAR (50),
								DEFINICAO VARCHAR(MAX))  


	insert into @RS_RETORNO
	SELECT   
			TABLE_CATALOG BANCO,  
			TABLE_NAME NOME,  
			'' + TABLE_NAME SQL_NOME,  
			TABLE_TYPE TIPO  ,
			''
		FROM   
			INFORMATION_SCHEMA.TABLES  
		WHERE  
			TABLE_NAME LIKE '%' + @TABELA + '%'
			AND TABLE_TYPE= 'BASE TABLE'
		union all
		SELECT   
			TABLE_CATALOG BANCO,  
			TABLE_NAME NOME,  
			'' + TABLE_NAME SQL_NOME,  
			'VIEW' TIPO ,
			VIEW_DEFINITION DEFINICAO
		FROM   
			INFORMATION_SCHEMA.VIEWS  
		WHERE  
			TABLE_NAME LIKE '%' + @TABELA + '%'
		ORDER BY  
			TABLE_NAME  

	declare @NM_TABLE as varchar(500), 
		@comando varchar(800), 
		@SqlQ varchar(max), 
		@NM_CL varchar(200)
		;

	DECLARE cAux CURSOR  fast_forward read_only for 
	select NOME from @RS_RETORNO 

	OPEN cAux
	FETCH NEXT FROM cAux INTO @NM_TABLE;

	WHILE @@FETCH_STATUS=0
	BEGIN

		set @comando = ' DECLARE cTbls CURSOR  fast_forward read_only for SELECT  COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS  WHERE  TABLE_NAME = ''' + @NM_TABLE + ''''; 
		set @SqlQ ='SELECT ' ;
		execute(@COMANDO)
		
		OPEN cTbls
		FETCH NEXT FROM cTbls INTO @NM_CL;
		WHILE @@FETCH_STATUS=0
		BEGIN
			set @SqlQ = @SqlQ + char(13)  + '' + @NM_CL + ',' 
			FETCH NEXT FROM cTbls INTO @NM_CL;
		END
		close cTbls
		deallocate cTbls

		SET @SqlQ = LEFT (@SqlQ , LEN(@SqlQ )-1);
		SET @SqlQ = @SqlQ +  char(13) +'FROM ' + char(13)    + @NM_TABLE

		UPDATE  R 
		SET SQL_NOME = @SqlQ
		FROM  
			@RS_RETORNO R
		WHERE
			NOME =@NM_TABLE;
			 
		FETCH NEXT FROM cAux INTO @NM_TABLE;
	end 

	SELECT * FROM @RS_RETORNO