Skip to content

Instantly share code, notes, and snippets.

@rogeriopradoj

rogeriopradoj/0-README.md

Last active March 30, 2020 06:37
Show Gist options
  • Save rogeriopradoj/528b64a1bba45cbb128196f77e7d6ee1 to your computer and use it in GitHub Desktop.
Save rogeriopradoj/528b64a1bba45cbb128196f77e7d6ee1 to your computer and use it in GitHub Desktop.
Download ZIP
CRIA catálogo de objetos servidores de banco de dados - sql server
Raw
0-README.md
Raw
1.sql
/*
* CATALOGO OBJETOS BANCO DE DADOS SUFUG/GERFU e vinculadas
*
* Objetivo é criar estrutura para tabela de catálogo de objetos
*
* Parte 1: cria catálogo temporário
*/
/*
* define unidade que vai ficar gravada referente esse servidor
*/
DECLARE @nome_unidade VARCHAR(100) = 'GERFU';
/*
* cria estrutura do catálogo (objetos e colunas)
*/
IF OBJECT_ID('tempdb..##TB_001_CATALOGO_OBJETOS_E_COLUNAS') IS NOT NULL
DROP TABLE ##TB_001_CATALOGO_OBJETOS_E_COLUNAS
;
CREATE TABLE ##TB_001_CATALOGO_OBJETOS_E_COLUNAS
(
NO_UNIDADE VARCHAR(100)
, NO_SERVIDOR NVARCHAR(128)
, DE_SERVIDOR NVARCHAR(300)
, DE_CURRENT_USER NVARCHAR(128)
, DE_SYSTEM_USER NVARCHAR(128)
, DE_ORIGINAL_LOGIN NVARCHAR(128)
, DE_SUSER_SNAME NVARCHAR(128)
, DH_GRAVACAO DATETIME2
, TABLE_CATALOG NVARCHAR(128)
, TABLE_SCHEMA NVARCHAR(128)
, TABLE_NAME NVARCHAR(128) NOT NULL
, COLUMN_NAME NVARCHAR(128) NOT NULL
, ORDINAL_POSITION INT
, COLUMN_DEFAULT NVARCHAR(4000)
, IS_NULLABLE VARCHAR(3)
, DATA_TYPE NVARCHAR(128)
, CHARACTER_MAXIMUM_LENGTH INT
, CHARACTER_OCTET_LENGTH INT
, NUMERIC_PRECISION TINYINT
, NUMERIC_PRECISION_RADIX TINYINT
, NUMERIC_SCALE INT
, DATETIME_PRECISION SMALLINT
, COLLATION_NAME NVARCHAR(128)
, DATABASE_DESCRIPTION SQL_VARIANT
, SCHEMA_DESCRIPTION SQL_VARIANT
, TABLE_DESCRIPTION SQL_VARIANT
, COLUMN_DESCRIPTION SQL_VARIANT
, ROW_COUNT BIGINT
, RESERVED_SIZE_KB BIGINT
, DATA_SIZE_KB BIGINT
, INDEX_SIZE_KB BIGINT
, UNUSED_SIZE_KB BIGINT
, RESERVED_SIZE_MB NUMERIC(36, 2)
, DATA_SIZE_MB NUMERIC(36, 2)
, INDEX_SIZE_MB NUMERIC(36, 2)
, UNUSED_SIZE_MB NUMERIC(36, 2)
, RESERVED_SIZE_GB NUMERIC(36,2)
, DATA_SIZE_GB NUMERIC(36,2)
, INDEX_SIZE_GB NUMERIC(36,2)
, UNUSED_SIZE_GB NUMERIC(36,2)
)
;
/*
* guarda catálogo de todos os objetos acessíveis em tabela temporária
*/
DECLARE @sql_command VARCHAR(MAX);
SELECT @sql_command = '
--SELECT has_dbaccess(SD.name) = 1;
-- SAIR SE FOR DATABASE DE SISTEMA
if ''?'' in (''master'',''model'',''msdb'',''tempdb'') return
USE ?
BEGIN TRY
INSERT INTO ##TB_001_CATALOGO_OBJETOS_E_COLUNAS
SELECT
''' + @nome_unidade + ''' NO_UNIDADE
, @@SERVERNAME NO_SERVIDOR
, @@VERSION DE_SERVIDOR
, CURRENT_USER DE_CURRENT_USER
, SYSTEM_USER DE_SYSTEM_USER
, ORIGINAL_LOGIN() DE_ORIGINAL_LOGIN
, SUSER_SNAME() DE_SUSER_SNAME
, GETDATE() DH_GRAVACAO
, isc.TABLE_CATALOG
, isc.TABLE_SCHEMA
, isc.TABLE_NAME
, isc.COLUMN_NAME
, isc.ORDINAL_POSITION
, isc.COLUMN_DEFAULT
, isc.IS_NULLABLE
, isc.DATA_TYPE
, isc.CHARACTER_MAXIMUM_LENGTH
, isc.CHARACTER_OCTET_LENGTH
, isc.NUMERIC_PRECISION
, isc.NUMERIC_PRECISION_RADIX
, isc.NUMERIC_SCALE
, isc.DATETIME_PRECISION
, isc.COLLATION_NAME
, sep_database.value DATABASE_DESCRIPTION
, sep_schema.value SCHEMA_DESCRIPTION
, sep_table.value TABLE_DESCRIPTION
, sep_column.value COLUMN_DESCRIPTION
, a1.rows AS ROW_COUNT
, (a1.reserved + ISNULL(a4.reserved, 0)) * 8 AS RESERVED_SIZE_KB
, a1.data * 8 AS DATA_SIZE_KB
, (
CASE
WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data
THEN (a1.used + ISNULL(a4.used, 0)) - a1.data
ELSE 0
END
) * 8 AS INDEX_SIZE_KB
, (
CASE
WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
ELSE 0
END
) * 8 AS UNUSED_SIZE_KB
, CAST(ROUND(((a1.reserved + ISNULL(a4.reserved, 0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS RESERVED_SIZE_MB
, CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DATA_SIZE_MB
, CAST(ROUND((
CASE
WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data
THEN (a1.used + ISNULL(a4.used, 0)) - a1.data
ELSE 0
END
) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS INDEX_SIZE_MB
, CAST(ROUND((
CASE
WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
ELSE 0
END
) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UNUSED_SIZE_MB
, CAST(ROUND(((a1.reserved + ISNULL(a4.reserved, 0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS RESERVED_SIZE_GB
, CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DATA_SIZE_GB
, CAST(ROUND((
CASE
WHEN (a1.used + ISNULL(a4.used, 0)) > a1.data
THEN (a1.used + ISNULL(a4.used, 0)) - a1.data
ELSE 0
END
) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS INDEX_SIZE_GB
, CAST(ROUND((
CASE
WHEN (a1.reserved + ISNULL(a4.reserved, 0)) > a1.used
THEN (a1.reserved + ISNULL(a4.reserved, 0)) - a1.used
ELSE 0
END
) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UNUSED_SIZE_GB
FROM
sys.schemas ss
INNER JOIN sys.tables st
ON 1 = 1
AND ss.schema_id = st.schema_id
INNER JOIN sys.columns sc
ON 1 = 1
AND st.object_id = sc.object_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS isc
ON 1 = 1
AND isc.TABLE_NAME = st.NAME
AND isc.COLUMN_NAME = sc.NAME
LEFT JOIN sys.extended_properties sep_database
ON 1 = 1
AND sep_database.class = 0
AND sep_database.major_id = 0
AND sep_database.minor_id = 0
AND sep_database.NAME = ''MS_Description''
LEFT JOIN sys.extended_properties sep_schema
ON 1 = 1
AND sep_schema.class = 3
AND ss.schema_id = sep_schema.major_id
AND sep_schema.minor_id = 0
AND sep_schema.NAME = ''MS_Description''
LEFT JOIN sys.extended_properties sep_table
ON 1 = 1
AND sep_table.class = 1
AND st.object_id = sep_table.major_id
AND sep_table.minor_id = 0
AND sep_table.NAME = ''MS_Description''
LEFT JOIN sys.extended_properties sep_column
ON 1 = 1
AND sep_column.class = 1
AND st.object_id = sep_column.major_id
AND sc.column_id = sep_column.minor_id
AND sep_column.NAME = ''MS_Description''
LEFT JOIN
(SELECT
ps.object_id,
SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
--===Remove the following comment for SQL Server 2014+
--WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
ON ( a1.object_id = st.object_id )
LEFT JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4
ON (a4.parent_id = a1.object_id)
ORDER BY isc.TABLE_CATALOG
, isc.TABLE_SCHEMA
, isc.TABLE_NAME
, isc.ORDINAL_POSITION
END TRY
BEGIN CATCH
--SELECT NULL WHERE 1 = 0
END CATCH
'
;
/*
* CURSOR PARA EXECUTAR A QUERY EM TODOS OS DATABASES
*/
SET NOCOUNT ON;
DECLARE @database_name VARCHAR(300)
DECLARE @sql_command_to_execute NVARCHAR(MAX)
DECLARE @database_names TABLE
(database_name VARCHAR(100))
INSERT INTO @database_names
( database_name )
--SELECT SD.name AS database_name FROM sys.databases SD;
SELECT SD.name AS database_name FROM sys.databases SD WHERE has_dbaccess(SD.name) = 1; -- apenas databases com acesso do usuário
DECLARE db_cursor CURSOR FOR SELECT database_name FROM @database_names
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @database_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_command_to_execute = REPLACE(@sql_command, '?', @database_name)
EXEC sp_executesql @sql_command_to_execute
FETCH NEXT FROM db_cursor INTO @database_name
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
/*
*
--DF7436SR659
Finish time Wed Nov 13 20:37:05 GMT-03:00 2019
1 row(s) fetched - 15.994s
Finish time Thu Nov 14 19:22:37 BRT 2019
1 row(s) fetched - 14.365s
--DF7436SR626
Finish time Thu Nov 14 19:23:16 BRT 2019
1 row(s) fetched - 564ms
*/
/*
* CATALOGO OBJETOS BANCO DE DADOS SUFUG/GERFU e vinculadas
*
* Objetivo é criar estrutura para tabela de catálogo de objetos
*
* Parte 2: consulta catálogo temporário
*/
--- CONSULTA CATÁLOGO
SELECT
*
FROM
##TB_001_CATALOGO_OBJETOS_E_COLUNAS WITH(NOLOCK)
WHERE 1 = 1
ORDER BY
NO_UNIDADE
, NO_SERVIDOR
, TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
;
/*
--DF7436SR659
66.603 row(s) fetched - 405ms (+7.74s)
14/11/2019 19h20
65.165 row(s) fetched - 545ms (+6.781s)
30/03/2020 19h20
68.678 - 1min04s
--DF7436SR626
14/11/2019 19h21
857 row(s) fetched - 11ms (+63ms)
30/03/2020 03h33
9.058 - 7s
*/
Raw
2.sql
/*
* CATALOGO OBJETOS BANCO DE DADOS SUFUG/GERFU e vinculadas
*
* Objetivo é criar estrutura para tabela de catálogo de objetos
*
* Parte 2: consulta catálogo temporário
*/
--- CONSULTA CATÁLOGO
SELECT
*
FROM
##TB_001_CATALOGO_OBJETOS_E_COLUNAS WITH(NOLOCK)
WHERE 1 = 1
ORDER BY
NO_UNIDADE
, NO_SERVIDOR
, TABLE_CATALOG
, TABLE_SCHEMA
, TABLE_NAME
, COLUMN_NAME
;
/*
--DF7436SR659
66.603 row(s) fetched - 405ms (+7.74s)
14/11/2019 19h20
65.165 row(s) fetched - 545ms (+6.781s)
--DF7436SR626
14/11/2019 19h21
857 row(s) fetched - 11ms (+63ms)
*/
Raw
3.sql
/*
* CATALOGO OBJETOS BANCO DE DADOS SUFUG/GERFU e vinculadas
*
* Objetivo é criar estrutura para tabela de catálogo de objetos
*
* Parte 3: inserir descrições (DATABASE, SCHEMA, TABLE/VIEW e COLUMN)
*/
--INSERE DESCRIPTION DO DATABASE
EXECUTE sp_addextendedproperty
@name = N'MS_Description'
, @value = 'Informações compartilhadas de forma pública no servidor'
--ATUALIZA DESCRIPTION DO DATABASE
EXEC sp_updateextendedproperty
@name = N'MS_Description'
, @value = 'Novos detalhes'
--INSERE DESCRIPTION DE SCHEMA
EXECUTE sp_addextendedproperty
@name = N'MS_Description'
, @value = 'Agrupamento padrão do DATABASE'
, @level0type = N'SCHEMA'
, @level0name = 'dbo'
--ATUALIZA DESCRIPTION DO SCHEMA
EXEC sp_updateextendedproperty
@name = N'MS_Description'
, @value = 'Novos detalhes'
, @level0type = N'SCHEMA'
, @level0name = 'dbo'
--INSERE DESCRIPTION DE OBJETO (TABELA, VIEW)
EXECUTE sp_addextendedproperty
@name = N'MS_Description'
, @value = 'Armazenar informações de metadados das tabelas no nosso servidor'
, @level0type = N'SCHEMA'
, @level0name = 'dbo'
, @level1type = N'TABLE'
, @level1name = 'INFOTB001_METADADOS'
--ATUALIZA DESCRIPTION DO OBJETO (TABELA, VIEW)
EXEC sp_updateextendedproperty
@name = N'MS_Description'
, @value = 'Novos detalhes'
, @level0type = N'SCHEMA'
, @level0name = 'dbo'
, @level1type = N'TABLE'
, @level1name = 'INFOTB001_METADADOS'
--INSERE DESCRIPTION DE COLUNA
EXECUTE sp_addextendedproperty
@name = N'MS_Description'
, @value = 'Data e horário da informação desse registro'
, @level0type = N'SCHEMA'
, @level0name = 'dbo'
, @level1type = N'TABLE'
, @level1name = 'INFOTB001_METADADOS'
, @level2type = N'COLUMN'
, @level2name = 'DH_ALTERACAO_REGISTRO'
--ATUALIZA DESCRIPTION DA COLUNA
EXEC sp_updateextendedproperty
@name = N'MS_Description'
, @value = 'Novos detalhes'
, @level0type = N'SCHEMA'
, @level0name = 'dbo'
, @level1type = N'TABLE'
, @level1name = 'INFOTB001_METADADOS'
, @level2type = N'COLUMN'
, @level2name = 'DH_ALTERACAO_REGISTRO'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment