SQL Server - How to Identify Columns and Parameters Data Type for Tables, Views, Stored Procedures, and Functions

Views: 15.306 views

Reading Time: 4 minutes

Speak guys!
In this quick post today, I would like to share with you how to use DMV's to identify the data type of columns in tables, views and return tables in functions of the type Table Valued Functions (TVF) and also the type of data in parameters of Functions and Stored Procedures in SQL Server, both custom and primitive types. Through a question you sent me on Whatsapp I had the idea to create this post and I hope it will be useful for you.

How to identify the data type of views and table columns

Code used to create table and test types
View source

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,

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

)

Query to identify column data types

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)

Result

A few points I would like to highlight about this query:

Identifies data types for table columns as well as views and return tables in Table Valued Function (TVF) functions
Returns the collation used by each column in the tables. Very useful to identify columns that use collation other than the default. Speaking of Collation, it's always good to remember this post by Fabrício Lima - Improve the performance of a query that uses ”like '% String%'” changing only the collation, which shows the stark difference in performance when using Windows collation (Latin1_%) and SQL Server collation (SQL_Latin1_%)
Allows you to fetch the varchar (max) columns to later try to identify the largest record and change the maximum size.
Lets you look up the nvarchar and nchar columns to later change the type to varchar or char (or vice versa), avoiding implicit conversion, which is very common when using the Entity Framework.
Lets you search for text and ntext columns, data types already marked deprecated, to analyze replacement with other more recommended data types
Returns some column properties, such as whether it is identity, NULLable, or calculated
Returns the name of the column data type as well as the primitive type (if the column uses custom types)
In addition to returning the maximum column size, it also returns precision and scale for data types such as numeric.

How to identify the data type of SP parameters and functions

Just as you can identify the data type of Views and Tables columns, you can also identify the parameter data type in Stored Procedures and Functions.

Code used to create test objects
View source

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 (

@Nome VARCHAR(128)

)

RETURNS @Retorno TABLE (

[object_id] INT,

[name] sysname

)

BEGIN

INSERT INTO @Retorno

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

RETURN

END

If you want to analyze parameter data types in Stored Procedures and Functions, you can use the script below:

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:

A few points I would like to highlight about this query:

Identifies data types for Stored Procedures (T-SQL and CLR) and Functions (Scalar, Table-Valued, Aggregate and CLR)
Returns the name of the column data type as well as the primitive type (if the column uses custom types)
Functions of type Scalar Function will always have the 0 parameter, which is the return of the function and is therefore classified as OUTPUT.
The Table Valued Function return table does not enter the parameter list (obviously), but YES, in the previous query, which lists views and tables.
Stored Procedures with OUTPUT parameters are correctly identified by the flag. Unlike Scalar Function, these parameters follow the normal order of declaration and there is no 0 parameter

Well guys, I warned that the post would be short and objective .. lol
I hope it will be useful to you on a daily basis and until the next article!

Strong hug!

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to identify the data type of columns and parameters of Tables, Views, Stored Procedures and Functions

How to identify the data type of views and table columns

How to identify the data type of SP parameters and functions

You may also like ...

Leave a Comment Cancel reply

How to identify the data type of views and table columns

How to identify the data type of SP parameters and functions

You may also like ...

SQL Server - How to monitor your favorite series by consuming a JSON Webservice with the JSON_VALUE function and using CLR (C #)

SQL Server - Using Resource Governor for greater control over server resources

SQL Server 2012 - Using the FORMAT function to apply masks and formatting to numbers and dates

Leave a Comment Cancel reply