SQL Server - Como identificar o tipo de dado de colunas e parâmetros de Tabelas, Views, Stored Procedures e Funções

Post Views 17,698 views

Reading time 4 minutes

Hey guys!
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
)

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,

Name 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)

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

CREATE PROCEDURE stpTeste2 (

@Param1 tpNome,

@Param2 varchar(100) OUTPUT

)

AS SELECT 1

CREATE PROCEDURE stpTeste (

@Param1 tpNome,

@Param2 varchar(100),

@Param3 VARCHAR(MAX),

@Param4 text

)

AS SELECT 1

CREATE FUNCTION fncRetornaNome (

@Param1 tpNome,

@Param2 VARCHAR (100),

@Param3 VARCHAR (MAX),

@Param4 TEXT

)

RETURNS VARCHAR (MAX)

BEGIN

RETURN 'Dirceu'

END

CREATE FUNCTION fncListaTabela (

@Name VARCHAR(128)

)

RETURNS @Retorno TABLE (

[object_id] INT,

[name] sysname

)

BEGIN

INSERT INTO @Retorno

SELECT [object_id], [name] FROM sys.objects WHERE [name] = @Name

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

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

Result:

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!

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

SQL Server – Como identificar o tipo de dado de colunas e parâmetros de Tabelas, Views, Stored Procedures e Funções

Como identificar o tipo de dado das colunas de views e tabelas

Como identificar o tipo de dado das parâmetros em SP e funções

You may also like...

Deixe uma respostaCancelar resposta

Como identificar o tipo de dado das colunas de views e tabelas

Como identificar o tipo de dado das parâmetros em SP e funções

You may also like...

SQL Server – Como criar uma AutoSoma (igual do Excel) utilizando Window functions (Running totals)

Arithmetic Overflow na view de catálogo sys.syscolumns do SQL Server 2008

SQL Server – Como identificar, monitorar e prevenir overflow em colunas com IDENTITY e SEQUENCE de tabelas com muitos registros

Deixe uma respostaCancelar resposta