¡Hola, chicos!
En esta publicación rápida de hoy, me gustaría compartir con ustedes cómo usar DMV para identificar el tipo de datos de columnas en tablas, vistas y tablas de retorno en funciones de tipo Funciones con valores de tabla (TVF) y también el tipo de datos en parámetros de funciones y procedimientos almacenados en SQL Server, tanto de tipo personalizado como primitivo. A través de una pregunta que me enviaron por Whatsapp tuve la idea de crear este post y espero que les sea de utilidad.

Cómo identificar el tipo de datos de la vista y las columnas de la tabla

Código utilizado para crear la tabla y los tipos de prueba.
Ver código fuente

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 los tipos de datos de las columnas.

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

Algunos puntos que me gustaría destacar sobre esta consulta:

  • Identifica los tipos de datos de las columnas de la tabla y también visualiza y devuelve tablas en funciones de función con valores de tabla (TVF)
  • Devuelve la intercalación utilizada por cada columna de las tablas. Muy útil para identificar columnas que utilizan una intercalación distinta a la predeterminada. Hablando de Colación, siempre es bueno recordar este post de Fabrício Lima – Mejore el rendimiento de una consulta que utiliza "como '%String%'" cambiando solo la intercalación, que muestra la marcada diferencia en el rendimiento cuando se utiliza la intercalación de Windows (Latin1_%) y la intercalación de SQL Server (SQL_Latin1_%)
  • Le permite buscar columnas varchar(max) para luego intentar identificar el registro más grande y cambiar el tamaño máximo.
  • Permite buscar las columnas nvarchar y nchar y luego cambiar el tipo a varchar o char (o viceversa), evitando la conversión implícita, algo muy común cuando se utiliza Entity Framework.
  • Le permite buscar columnas text y ntext, tipos de datos ya marcados como obsoletos, para analizar el reemplazo con otros tipos de datos más recomendados.
  • Devuelve algunas propiedades de la columna, como si es identidad, NULLable o calculada
  • Devuelve el nombre del tipo de datos de la columna y también el tipo primitivo (si la columna usa tipos personalizados)
  • Además de devolver el tamaño máximo de columna, también devuelve precisión y escala para tipos de datos como numéricos.

Cómo identificar el tipo de datos de los parámetros en SP y funciones

Así como es posible identificar el tipo de datos de las columnas Vistas y Tablas, también es posible identificar el tipo de datos de los parámetros en Procedimientos y funciones almacenados.

Código utilizado para crear los objetos de prueba.
Ver código fuente

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

Si desea analizar los tipos de datos de los parámetros en funciones y procedimientos almacenados, puede utilizar el siguiente script:

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:

Algunos puntos que me gustaría destacar sobre esta consulta:

  • Identifica los tipos de datos de Procedimientos Almacenados (T-SQL y CLR) y Funciones (Escalares, con valores de tabla, Agregados y CLR)
  • Devuelve el nombre del tipo de datos de la columna y también el tipo primitivo (si la columna usa tipos personalizados)
  • Las funciones del tipo Función Escalar siempre tendrán el parámetro 0, que es el retorno de la función y por tanto se clasifica como SALIDA
  • La tabla de retorno de la función con valores de tabla no aparece en la lista de parámetros (obviamente), pero sí, en la consulta anterior, que enumera vistas y tablas.

  • Los procedimientos almacenados con parámetros de salida (SALIDA) se identifican correctamente mediante la bandera. A diferencia de lo que sucede en la Función Escalar, estos parámetros siguen el orden normal de declaración y no existe ningún parámetro 0

Bueno chicos, les advertí que el post sería corto y objetivo... jajaja
Espero que te sea útil en tu vida diaria y ¡nos vemos en el próximo artículo!

¡Gran abrazo!