Snippet Name: Implicit_Conversions_in_SQL
Tags:
Created Date: 2025-06-16 13:12:48
Last Modified Date: 2025-05-31 10:31:48
Dependencies:
DBA Notes:
Sample Output:
Title: Implicit Conversions in SQL
Author: user
i have a sql server that i did an in place upgrade form SQL2016 to SQL 2019. after changing compatibility, i encountered a couple of procs and functiosn that had implicit converison from varchar to uniqueidentifier, and nvarchar to xml. to fix that, i had to add explicit converisons. can you help me find any other implicit conversiosn in sys.sql_modules and sys refrences?
Author: ChatGPT
Lowell performed an in-place upgrade from SQL Server 2016 to SQL Server 2019 and encountered implicit conversions in stored procedures and functions from `varchar` to `uniqueidentifier` and `nvarchar` to `xml`. To resolve the issue, explicit conversions were added.
Author: tool
Model set context updated.
Author: ChatGPT
To identify any other implicit conversions in your SQL Server code, you can search through the `sys.sql_modules` for common patterns that might indicate an implicit conversion. For example, you can look for variables or columns that are being compared or assigned across different data types.
Here???s a query that might help you find potential implicit conversions in your stored procedures, functions, and other modules:
```sql
WITH ConversionCandidates AS (
SELECT
m.object_id,
OBJECT_NAME(m.object_id) AS object_name,
m.definition AS module_definition,
CASE
-- Common implicit conversion types
WHEN m.definition LIKE '%varchar%' AND m.definition LIKE '%uniqueidentifier%' THEN 'Potential varchar to uniqueidentifier conversion'
WHEN m.definition LIKE '%nvarchar%' AND m.definition LIKE '%xml%' THEN 'Potential nvarchar to xml conversion'
WHEN m.definition LIKE '%int%' AND m.definition LIKE '%nvarchar%' THEN 'Potential int to nvarchar conversion'
-- Add more patterns as needed
ELSE 'Unknown'
END AS conversion_type
FROM sys.sql_modules AS m
WHERE m.definition LIKE '%varchar%'
OR m.definition LIKE '%nvarchar%'
OR m.definition LIKE '%uniqueidentifier%'
OR m.definition LIKE '%xml%'
OR m.definition LIKE '%int%'
)
SELECT *
FROM ConversionCandidates
WHERE conversion_type <> 'Unknown';
```
This query looks for common implicit conversion cases in the `sys.sql_modules` view. You can extend the patterns in the `CASE` statement to include more types as necessary. Once you identify them, review the code to see if explicit conversions are needed to avoid performance issues or potential errors.
Author: user
the issue is more complex than that. we cannot assume the procedure definition has the data types as searchable strings. i think we need to search sys.sql_expression_dependencies , and join to sys.columns and sys.objects to search for procedure sthat reference any table with a uniqueidentifier or xml data type column reference
Author: ChatGPT
Lowell is looking to search for procedures that reference tables with `uniqueidentifier` or `xml` data type columns by using `sys.sql_expression_dependencies`, joining it with `sys.columns` and `sys.objects`.
Author: tool
Model set context updated.
Author: ChatGPT
You're right! To find procedures or functions that reference tables with `uniqueidentifier` or `xml` data types, you can query `sys.sql_expression_dependencies`, `sys.columns`, and `sys.objects`. Here's a query to achieve that:
```sql
WITH TableColumnDataTypes AS (
SELECT
o.object_id AS table_id,
o.name AS table_name,
c.name AS column_name,
t.name AS data_type
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE o.type = 'U' -- User tables
AND (t.name = 'uniqueidentifier' OR t.name = 'xml')
),
ProcReferences AS (
SELECT
d.referencing_id AS proc_id,
OBJECT_NAME(d.referencing_id) AS proc_name,
d.referenced_id AS table_id
FROM sys.sql_expression_dependencies d
WHERE d.referenced_id IN (SELECT table_id FROM TableColumnDataTypes)
),
FinalResults AS (
SELECT
pr.proc_name,
tc.table_name,
tc.column_name,
tc.data_type
FROM ProcReferences pr
JOIN TableColumnDataTypes tc ON pr.table_id = tc.table_id
)
SELECT *
FROM FinalResults;
```
### Explanation:
1. **TableColumnDataTypes:** This CTE finds all user tables that contain columns of type `uniqueidentifier` or `xml`.
2. **ProcReferences:** This finds all procedures or functions that reference any of these tables using `sys.sql_expression_dependencies`.
3. **FinalResults:** The final result shows which procedures reference which tables and their specific columns with `uniqueidentifier` or `xml` data types.
This query will help identify procedures that might be subject to implicit conversions based on the data types in the referenced tables.
Author: user
that is a good start, but we need to check if the sys.sql_moudles has teh column name in it's body as well
Author: ChatGPT
Got it! To refine the query, we can check if the procedure or function body (from `sys.sql_modules`) contains the column names that are of `uniqueidentifier` or `xml` data types. Here's an updated query:
```sql
WITH TableColumnDataTypes AS (
SELECT
o.object_id AS table_id,
o.name AS table_name,
c.name AS column_name,
t.name AS data_type
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE o.type = 'U' -- User tables
AND (t.name = 'uniqueidentifier' OR t.name = 'xml')
),
ProcReferences AS (
SELECT
d.referencing_id AS proc_id,
OBJECT_NAME(d.referencing_id) AS proc_name,
d.referenced_id AS table_id
FROM sys.sql_expression_dependencies d
WHERE d.referenced_id IN (SELECT table_id FROM TableColumnDataTypes)
),
ModuleSearch AS (
SELECT
pr.proc_name,
tc.table_name,
tc.column_name,
tc.data_type,
m.definition AS proc_definition
FROM ProcReferences pr
JOIN TableColumnDataTypes tc ON pr.table_id = tc.table_id
JOIN sys.sql_modules m ON pr.proc_id = m.object_id
WHERE m.definition LIKE '%' + tc.column_name + '%'
)
SELECT *
FROM ModuleSearch;
```
### Changes made:
1. **ModuleSearch:** This part joins `ProcReferences` with `sys.sql_modules` and checks if the procedure's body (`definition`) contains the column name (`tc.column_name`).
2. **LIKE condition:** The `LIKE '%' + tc.column_name + '%'` clause ensures that the column name from the `TableColumnDataTypes` appears somewhere in the procedure body.
This will help narrow down the search to procedures that not only reference tables with `uniqueidentifier` or `xml` columns but also directly use those columns in their code.
Author: user
this is really a good start. now, can we add a test for the charindex of '=' before or after the column name, within say, 10 characters
Author: ChatGPT
Great idea! We can refine the query by using `CHARINDEX` to check if the column name is near an assignment (`=`) operator, either before or after the column name, within 10 characters. Here's the updated query:
```sql
WITH TableColumnDataTypes AS (
SELECT
o.object_id AS table_id,
o.name AS table_name,
c.name AS column_name,
t.name AS data_type
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE o.type = 'U' -- User tables
AND (t.name = 'uniqueidentifier' OR t.name = 'xml')
),
ProcReferences AS (
SELECT
d.referencing_id AS proc_id,
OBJECT_NAME(d.referencing_id) AS proc_name,
d.referenced_id AS table_id
FROM sys.sql_expression_dependencies d
WHERE d.referenced_id IN (SELECT table_id FROM TableColumnDataTypes)
),
ModuleSearch AS (
SELECT
pr.proc_name,
tc.table_name,
tc.column_name,
tc.data_type,
m.definition AS proc_definition,
-- Calculate the position of the column name in the procedure body
CHARINDEX(tc.column_name, m.definition) AS col_position
FROM ProcReferences pr
JOIN TableColumnDataTypes tc ON pr.table_id = tc.table_id
JOIN sys.sql_modules m ON pr.proc_id = m.object_id
WHERE m.definition LIKE '%' + tc.column_name + '%'
),
FilterAssignments AS (
SELECT
ms.proc_name,
ms.table_name,
ms.column_name,
ms.data_type,
ms.proc_definition,
ms.col_position,
-- Check for '=' before or after the column name within 10 characters
CASE
WHEN CHARINDEX('=', ms.proc_definition, ms.col_position - 10) > 0
AND CHARINDEX('=', ms.proc_definition, ms.col_position - 10) < ms.col_position
THEN 'Before'
WHEN CHARINDEX('=', ms.proc_definition, ms.col_position + LEN(ms.column_name)) > 0
AND CHARINDEX('=', ms.proc_definition, ms.col_position + LEN(ms.column_name)) <= ms.col_position + 10 + LEN(ms.column_name)
THEN 'After'
ELSE 'None'
END AS equality_check
FROM ModuleSearch ms
)
SELECT *
FROM FilterAssignments
WHERE equality_check IN ('Before', 'After');
```
### Explanation:
1. **ModuleSearch:** This part now computes the position of the column name in the procedure body using `CHARINDEX(tc.column_name, m.definition)`.
2. **FilterAssignments:**
- We use `CHARINDEX('=', ...)` to check if an `=` is found within 10 characters before or after the column name position.
- If an `=` is found before the column name within 10 characters, we mark it as `'Before'`. If after, it is marked as `'After'`.
3. **Final filtering:** The final result includes only those procedures where an `=` is found near the column name (`Before` or `After`).
This approach helps pinpoint potential assignments where implicit conversions might occur.
Author: user
that was extremely helpful. i made two minor changes. i aded a QuickReview column that has a substring to show the assignment of the equals sign for visual review, and also joined to sys.objects so i could get the object type, whether it was a function, trigger, or procedure. here's my updated code: WITH TableColumnDataTypes AS (
SELECT
o.object_id AS table_id,
o.name AS table_name,
c.name AS column_name,
t.name AS data_type
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE o.type = 'U' -- User tables
AND (t.name = 'uniqueidentifier' OR t.name = 'xml')
),
ProcReferences AS (
SELECT
d.referencing_id AS proc_id,
OBJECT_NAME(d.referencing_id) AS proc_name,
d.referenced_id AS table_id
FROM sys.sql_expression_dependencies d
WHERE d.referenced_id IN (SELECT table_id FROM TableColumnDataTypes)
),
ModuleSearch AS (
SELECT
pr.proc_id,
pr.proc_name,
tc.table_name,
tc.column_name,
tc.data_type,
m.definition AS proc_definition,
-- Calculate the position of the column name in the procedure body
CHARINDEX(tc.column_name, m.definition) AS col_position
FROM ProcReferences pr
JOIN TableColumnDataTypes tc ON pr.table_id = tc.table_id
JOIN sys.sql_modules m ON pr.proc_id = m.object_id
WHERE m.definition LIKE '%' + tc.column_name + '%'
),
FilterAssignments AS (
SELECT
ms.proc_id,
ms.proc_name,
ms.table_name,
ms.column_name,
ms.data_type,
ms.proc_definition,
ms.col_position,
-- Check for '=' before or after the column name within 10 characters
CASE
WHEN CHARINDEX('=', ms.proc_definition, ms.col_position - 10) > 0
AND CHARINDEX('=', ms.proc_definition, ms.col_position - 10) < ms.col_position
THEN 'Before'
WHEN CHARINDEX('=', ms.proc_definition, ms.col_position + LEN(ms.column_name)) > 0
AND CHARINDEX('=', ms.proc_definition, ms.col_position + LEN(ms.column_name)) <= ms.col_position + 10 + LEN(ms.column_name)
THEN 'After'
ELSE 'None'
END AS equality_check
FROM ModuleSearch ms
)
SELECT objz.type_desc,*,SUBSTRING(proc_definition,col_position -20,col_position + 20) AS QuickReview
FROM FilterAssignments
INNER JOIN sys.objects objz ON FilterAssignments.proc_id = &#91;objz].&#91;object_id]
WHERE equality_check IN ('Before', 'After');
Author: ChatGPT
Lowell added enhancements to the implicit conversion search query by including a `QuickReview` column for visual review of the assignment and joining to `sys.objects` to get the object type (procedure, function, trigger).
Author: tool
Model set context updated.
Author: ChatGPT
Your improvements to the query are fantastic! Adding the `QuickReview` column for visual inspection and joining with `sys.objects` to identify the object type really rounds out the solution. This will make it much easier to quickly spot problematic assignments and understand the context within various object types.
If you need further adjustments or have more questions, feel free to reach out!