Viji juntou geral versao 1.0
USE SEU <<<<<<< BANCO >>>>>>>>>>>
-- ======================== INICIO =========================
-- ======================== ESTRUTURA TABELAS DICIONARIO DE DADOS =========================
DECLARE @w_TABLE_CATALOG char(50)
DECLARE @w_TABLE_NAME char(25)
DECLARE @w_TABLE_NAME_aux char(25)
DECLARE @w_COLUMN_NAME char(25)
DECLARE @w_DATA_TYPE char(15)
DECLARE @w_CHARACTER_MAXIMUM_LENGTH char(5)
SET @w_TABLE_NAME_aux=' '
DECLARE dicicionario CURSOR SCROLL FOR
SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE,
ISNULL(CHARACTER_MAXIMUM_LENGTH,0)
FROM INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME
OPEN dicicionario
FETCH FIRST FROM dicicionario INTO @w_TABLE_CATALOG,@w_TABLE_NAME,@w_COLUMN_NAME,@w_DATA_TYPE,@w_CHARACTER_MAXIMUM_LENGTH
WHILE @@FETCH_STATUS = 0
BEGIN
if @w_TABLE_NAME_aux != @w_TABLE_NAME
BEGIN
if @w_TABLE_NAME_aux != ' '
BEGIN
-- ======================== INICIO =========================
-- ======================== CHAVES ESTRANGEIRAS DICIONARIO DE DADOS =========================
-- DECLARE @w_TABLE_CATALOG char(50)
--
DECLARE @w_Cab_Name char(25)
DECLARE @w_Name char(25)
SET @w_Name='ChaveEstrangeira'
--
DECLARE @w_Cab_Parent_Object_ID char(25)
DECLARE @w_Parent_Object_ID char(25)
SET @w_Cab_Parent_Object_ID='TabelaFilho'
--
DECLARE @w_Cab_Referenced_Object_ID char(25)
DECLARE @w_Referenced_Object_ID char(25)
SET @w_Cab_Referenced_Object_ID='TabelaPai'
--
PRINT '------------------------------------------------------------------------'
PRINT 'nome da tabela Tabela pai Tabela Filha'
PRINT '------------------------------------------------------------------------'
DECLARE w_chave_estrangeira CURSOR SCROLL FOR SELECT Name , OBJECT_NAME(Parent_Object_ID) , OBJECT_NAME(Referenced_Object_ID)
FROM SYS.FOREIGN_KEYS where OBJECT_NAME(Parent_Object_ID)=@w_TABLE_NAME_aux
OPEN w_chave_estrangeira
FETCH FIRST FROM w_chave_estrangeira INTO @w_Name,@w_Parent_Object_ID,@w_Referenced_Object_ID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @w_Name+' '+@w_Parent_Object_ID+' '+@w_Referenced_Object_ID
FETCH NEXT FROM w_chave_estrangeira INTO @w_Name,@w_Parent_Object_ID,@w_Referenced_Object_ID
END
CLOSE w_chave_estrangeira
DEALLOCATE w_chave_estrangeira
PRINT '------------------------------------------------------------------------'
-- ======================== FIM =========================
-- ======================== CHAVES ESTRANGEIRAS DICIONARIO DE DADOS =========================
-- SET @w_TABLE_NAME_aux ='%<<TABELA>>%' deu certo
-- ======================== INICIO =========================
-- ======================== FN = Function, P = Procedure, TR = Trigger, V = View =========================
-- ======================== DICIONARIO DE DADOS =========================
-- DECLARE @w_TABLE_NAME_aux VARCHAR(25)
DECLARE @w_TABLE_NAME_aux_FN_P_TR_V VARCHAR(25)
-- SET @w_TABLE_NAME_aux = '<<TABELA>>';
DECLARE @w_Name_FN_P_TR_V char(50)
DECLARE @w_Type_FN_P_TR_V char(5)
DECLARE @w_Text_FN_P_TR_V nvarchar(4000) -- Declaração Rapida e Facil para Variavel Text
DECLARE @w_Type_aux_FN_P_TR_V char(5)
SET @w_Type_aux_FN_P_TR_V = ''
--
DECLARE w_FN_P_TR_V CURSOR SCROLL FOR
SELECT A.NAME, A.TYPE, B.TEXT
FROM SYSOBJECTS A
INNER JOIN SYSCOMMENTS B
ON A.ID = B.ID
WHERE B.TEXT LIKE '%'+@w_TABLE_NAME_aux+'%'
ORDER BY A.TYPE, A.NAME
OPEN w_FN_P_TR_V
FETCH FIRST FROM w_FN_P_TR_V INTO @w_Name_FN_P_TR_V,@w_Type_FN_P_TR_V,@w_Text_FN_P_TR_V
WHILE @@FETCH_STATUS = 0
BEGIN
IF @w_Type_FN_P_TR_V != @w_Type_aux_FN_P_TR_V
BEGIN
SET @w_Type_aux_FN_P_TR_V = @w_Type_FN_P_TR_V
PRINT '*------------------------------------------------------------------------*'
IF @w_Type_aux_FN_P_TR_V='FN' BEGIN PRINT 'Function' END
IF @w_Type_aux_FN_P_TR_V='P' BEGIN PRINT 'Procedure' END
IF @w_Type_aux_FN_P_TR_V='TR' BEGIN PRINT 'Trigger' END
IF @w_Type_aux_FN_P_TR_V='V' BEGIN PRINT 'View' END
PRINT '*------------------------------------------------------------------------*'
END
PRINT '------------------------------------------------------------------------'
PRINT @w_Name_FN_P_TR_V
PRINT '------------------------------------------------------------------------'
PRINT @w_Text_FN_P_TR_V
FETCH NEXT FROM w_FN_P_TR_V INTO @w_Name_FN_P_TR_V,@w_Type_FN_P_TR_V,@w_Text_FN_P_TR_V
END
CLOSE w_FN_P_TR_V
DEALLOCATE w_FN_P_TR_V
PRINT '------------------------------------------------------------------------'
-- ======================== FIM =========================
-- ======================== FN = Function, P = Procedure, TR = Trigger, V = View =========================
-- ======================== DICIONARIO DE DADOS =========================
/*
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
*/
END
SET @w_TABLE_NAME_aux = @w_TABLE_NAME
PRINT '------------------------------------------------------------------------'
PRINT @w_TABLE_CATALOG+' '+@w_TABLE_NAME_aux
PRINT '------------------------------------------------------------------------'
END
PRINT @w_COLUMN_NAME+' '+@w_DATA_TYPE+' '+@w_CHARACTER_MAXIMUM_LENGTH
FETCH NEXT FROM dicicionario INTO @w_TABLE_CATALOG,@w_TABLE_NAME,@w_COLUMN_NAME,@w_DATA_TYPE,@w_CHARACTER_MAXIMUM_LENGTH
END
CLOSE dicicionario
DEALLOCATE dicicionario
-- ======================== FIM =========================
-- ======================== ESTRUTURA TABELAS DICIONARIO DE DADOS =========================