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

CREATE TYPE dbo.tpNome FROM VARCHAR(60) 
CREATE TYPE dbo.tpDinheiro FROM NUMERIC(18, 2)

CREATE TABLE dbo.Teste_Tipo (
    Id INT,
    Id_Big BIGINT,
    Nome tpNome,
    Binario VARBINARY(MAX),
    Texto TEXT,
    Texto_UFC NTEXT,
    Caractere CHAR(10),
    Caractere_UTF NCHAR(15),
    Campo_XML XML,
    Booleano BIT,
    Numerico NUMERIC(21, 6),
    String1 VARCHAR(101),
    String2 VARCHAR(MAX),
    String3 NVARCHAR(122),
    String4 NVARCHAR(MAX),
    Dinheiro tpDinheiro
)

Consulta para identificar os tipos de dados das colunas

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)

Resultado

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

GO
CREATE PROCEDURE stpTeste2 (
    @Param1 tpNome,
    @Param2 varchar(100) OUTPUT
)
AS SELECT 1


GO
CREATE PROCEDURE stpTeste (
    @Param1 tpNome,
    @Param2 varchar(100),
    @Param3 VARCHAR(MAX),
    @Param4 text
)
AS SELECT 1


GO
CREATE FUNCTION fncRetornaNome (
    @Param1 tpNome, 
    @Param2 VARCHAR (100), 
    @Param3 VARCHAR (MAX), 
    @Param4 TEXT
)
RETURNS VARCHAR (MAX)
AS
BEGIN
    RETURN 'Dirceu'
END


GO
CREATE FUNCTION fncListaTabela (
    @Nome VARCHAR(128)
)
RETURNS @Retorno TABLE (
    [object_id] INT,
    [name] sysname
)
AS
BEGIN
    
    INSERT INTO @Retorno
    SELECT [object_id], [name] FROM sys.objects WHERE [name] = @Nome

    RETURN

END

Caso você queira analisar os tipos de dados de parâmetros em Stored Procedures e Funções, pode utilizar o script abaixo:

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

Resultado:

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!