Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.428 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • December 28th, 2018
  • 0
  • Database DMVs and Catalog Views Maintenance SQL Server

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

Views: 4.690
Reading Time: 4 minutes

Hey 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

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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

Transact-SQL
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)

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

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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

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

Transact-SQL
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

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!

tags: sqlsql server

You may also like ...

  • SQL Server - How to Avoid Brute Force Attacks on Your Database

  • How to estimate how much time is left to finish the backup on SQL Server?

  • SQL Server - How to send the contents of a table or query in the body of the email as HTML

  • Next What has changed in SQL Server 2012 in relation to T-SQL - In the view of Developers
  • Previous Power BI - Who are the Microsoft MVP's in Brazil?

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.