GenerateJoins

				
--**************************************
-- Name: GenerateJoins
-- Description:Generate complete joins.
-- By: Sherenato
--
-- Inputs:Sp_GeraJoin <tabela1>,<alias_tabela_1<optional>>,<tabela2>,<alias_tabela_2<optional>>
--
-- Returns:SELECT * FROM
TB1 T1 JOIN TB2 T2
ON T1.ID = T2.ID
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=760&lngWId=5--for details.--**************************************

CREATE PROCEDURE Sp_GeraJoin (@Tb1 varchar(50), @AliasTb1 varchar(50)=NULL, @Tb2 varchar(50), @AliasTb2 varchar(50)=NULL)
as
SET NOCOUNT ON
IF @Tb1 IS NULL or @Tb2 is null BEGIN
	PRINT 'Sintaxe: Sp_GeraJoin <tabela1>,<alias_tabela_1<optional>>,<tabela2>,<alias_tabela_2<optional>>'
	RETURN
end
SELECT stmt
FROM (
	SELECT -2 AS pos, 'SELECT 		* FROM	' AS stmt
	UNION ALL
	SELECT -1 AS pos, '		' + UPPER(Ltrim(Rtrim(@Tb1))) + ' ' + UPPER(ISNULL(@AliasTb1,@Tb1)) + ' (NOLOCK)'
	UNION ALL
	SELECT 0 AS pos, 'JOIN		' + UPPER(Ltrim(Rtrim(@Tb2))) + ' ' + UPPER(ISNULL(@AliasTb2,@Tb2)) + ' (NOLOCK) '
	UNION ALL
	SELECT c.colorder AS pos, 'ON		' + UPPER(ISNULL(@AliasTb1,@Tb1)) + '.' + UPPER(c.name) + '		= ' + UPPER(ISNULL(@AliasTb2,@Tb2)) + '.' + UPPER(c.name)
	FROM sysindexes i, syscolumns c, sysobjects o
	WHERE	o.id = Object_id(@Tb1)
		AND o.id = c.id
		AND o.id = i.id
		AND (i.status & 0x800) = 0x800
		AND c.name = index_col (@Tb1, i.indid, 1)
	UNION ALL
	SELECT c.colorder AS pos, 'AND		' + UPPER(ISNULL(@AliasTb1,@Tb1)) + '.' + UPPER(c.name) + '			= ' + UPPER(ISNULL(@AliasTb2,@Tb2)) + '.' + UPPER(c.name)
	FROM sysindexes i, syscolumns c, sysobjects o
	WHERE	o.id = Object_id(@Tb1)
		AND o.id = c.id
		AND o.id = i.id
		AND (i.status & 0x800) = 0x800
		AND (c.name = index_col (@Tb1, i.indid, 2) or
		 c.name = index_col (@Tb1, i.indid, 3) OR
		 c.name = index_col (@Tb1, i.indid, 4) OR
		 c.name = index_col (@Tb1, i.indid, 5) OR
		 c.name = index_col (@Tb1, i.indid, 6) OR
		 c.name = index_col (@Tb1, i.indid, 7) OR
		 c.name = index_col (@Tb1, i.indid, 8) OR
		 c.name = index_col (@Tb1, i.indid, 9) OR
		 c.name = index_col (@Tb1, i.indid, 10) OR
		 c.name = index_col (@Tb1, i.indid, 11) OR
		 c.name = index_col (@Tb1, i.indid, 12) OR
		 c.name = index_col (@Tb1, i.indid, 13) OR
		 c.name = index_col (@Tb1, i.indid, 14) OR
		 c.name = index_col (@Tb1, i.indid, 15) OR
		 c.name = index_col (@Tb1, i.indid, 16))
) AS st
ORDER BY pos
RETURN