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
Query to identify column data types
|
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
If you want to analyze parameter data types in Stored Procedures and Functions, you can use the script below:
| 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!
