Fala pessoal!
Nesse post rápido de hoje, eu gostaria de compartilhar com vocês como utilizar DMV’s para identificar o tipo de dado das colunas em tabelas, views e tabelas de retorno em funções do tipo Table Valued Functions (TVF) e também o tipo de dado em parâmetros de Funções e Stored Procedures no SQL Server, tanto tipos personalizados quanto primitivos. Através de uma dúvida que me enviaram no Whatsapp eu tive a ideia de criar esse post e espero que seja útil para vocês.
Como identificar o tipo de dado das colunas de views e tabelas
Código utilizado para criar a tabela e os tipos de teste
Visualizar código-fonte
Consulta para identificar os tipos de dados das colunas
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT A.[name] AS tabela, B.[name] AS coluna, C.[name] AS tipo, D.[name] AS tipo_primitivo, B.max_length, (CASE WHEN B.max_length < 1 THEN 'MAX' ELSE CAST(B.max_length AS VARCHAR(10)) END) AS ds_max_length, B.[precision], B.scale, B.collation_name, B.is_nullable, B.is_identity, B.is_computed, B.is_xml_document FROM sys.objects A JOIN sys.columns B ON B.[object_id] = A.[object_id] JOIN sys.types C ON B.user_type_id = C.user_type_id JOIN sys.types D ON B.system_type_id = D.user_type_id WHERE A.is_ms_shipped = 0 -- AND B.collation_name <> DATABASEPROPERTYEX(DB_NAME(), 'Collation') -- Collation da coluna diferente da collation do database -- AND B.collation_name <> DATABASEPROPERTYEX('tempdb', 'Collation') -- Collation da coluna diferente da collation do tempdb -- AND B.max_length < 1 AND D.[name] = 'varchar' -- colunas varchar(MAX) |
Alguns pontos que eu gostaria de destacar sobre essa consulta:
- Identifica os tipos de dados das colunas de tabelas e também de views e tabelas de retorno em funções do tipo Table Valued Function (TVF)
- Retorna a collation utilizada por cada coluna das tabelas. Bem útil para identificar as colunas que usam a collation diferente do padrão. Por falar em Collation, sempre é bom relembrar esse post do Fabrício Lima – Improve the performance of a query that uses ” like ‘%String%’ ” changing only the collation, que mostra a diferença gritante de performance ao utilizar collation do Windows (Latin1_%) e collation do SQL Server (SQL_Latin1_%)
- Permite buscar as colunas varchar(max) para posteriormente, tentar identificar o maior registro e alterar o tamanho máximo
- Permite buscar as colunas nvarchar e nchar para posteriormente, alterar o tipo para varchar ou char (ou vice-versa), evitando conversão implícita, algo muito comum ao utilizar o Entity Framework
- Permite buscar as colunas do tipo text e ntext, tipos de dados já marcados como deprecated, para analisar a substituição por outros tipos de dados mais recomendáveis
- Retorna algumas propriedades das colunas, como se ela é identity, NULLable ou se é calculada
- Retorna o nome do tipo de dado da coluna e também o tipo primitivo (caso a coluna utilize tipos personalizados)
- Além de retornar o tamanho máximo da coluna, retorna também a precisão e escala para tipos de dados como numeric
Como identificar o tipo de dado das parâmetros em SP e funções
Assim como é possível identificar o tipo de dados das colunas de Views e Tabelas, também é possível identificar o tipo de dados de parâmetros em Stored Procedures e Funções.
Código utilizado para criar os objetos de teste
Visualizar código-fonte
Caso você queira analisar os tipos de dados de parâmetros em Stored Procedures e Funções, pode utilizar o script abaixo:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT C.[name] AS [schema], A.[name] AS [objeto], A.[type_desc] AS [tipo], B.parameter_id AS [parametro_numero], B.[name] AS [parametro_nome], D.[name] AS [tipo], E.[name] AS [tipo_primitivo], B.max_length AS [parametro_tamanho], B.is_output AS [output] FROM sys.objects A JOIN sys.parameters B ON A.[object_id] = B.[object_id] JOIN sys.schemas C ON C.[schema_id] = A.[schema_id] JOIN sys.types D ON B.user_type_id = D.user_type_id JOIN sys.types E ON B.system_type_id = E.user_type_id WHERE A.[type] IN ( 'P', 'FN', 'AF', 'FS', 'FT', 'PC', 'TF' ) ORDER BY [Schema], A.[name], B.parameter_id |
Alguns pontos que eu gostaria de destacar sobre essa consulta:
- Identifica os tipos de dados de Stored Procedures (T-SQL e CLR) e Funções (Scalar, Table-Valued, Aggregate e CLR)
- Retorna o nome do tipo de dado da coluna e também o tipo primitivo (caso a coluna utilize tipos personalizados)
- Funções do tipo Scalar Function sempre terão o parâmetro 0, que é o retorno da função e por isso é classificado como OUTPUT
- A tabela de retorno de Funções do tipo Table Valued Function não entra na lista de parâmetros (obviamente), mas entra SIM, na query anterior, que lista views e tabelas
- Stored Procedures com parâmetros de saída (OUTPUT) são corretamente identificados pela flag. Diferente do que ocorre na Scalar Function, esses parâmetros seguem a ordem normal de declaração e não há parâmetro 0
Bom pessoal, eu avisei que o post seria curto e objetivo.. rs
Espero que ele seja útil para vocês no dia a dia e até o próximo artigo!
Forte abraço!