No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.
This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:
And now it has been delivered in the following versions:
In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS.
Here is an example:
USE tempdb;
GO
CREATE TABLE dbo.x(a char(1));
INSERT dbo.x(a) VALUES('foo');
GO
Result in all supported versions prior to SQL Server 2019:
Msg 8152, Level 16, State 30, Line 5
String or binary data would be truncated.
The statement has been terminated.
Now, on SQL Server 2019 CTPs, with the trace flag enabled:
DBCC TRACEON(460);
GO
INSERT dbo.x(a) VALUES('foo');
GO
DROP TABLE dbo.x;
DBCC TRACEOFF(460);
Result shows the table, the column, and the (truncated, not full) value:
Msg 2628, Level 16, State 1, Line 11
String or binary data would be truncated in table 'tempdb.dbo.x', column 'a'. Truncated value: 'f'.
The statement has been terminated.
Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your "automagic" code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG's equivalent is. Or, since that just means you'll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you're already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn't have to do any brute-force guessing at all.
Currently there is no syntax directly supporting what you are trying to do. As you probably know, names cannot be parametrised in a SQL statement. That means that when you need to substitute names from column values of another table, you have to use dynamic SQL: first build the query string and then execute it. There is just no working around using dynamic SQL in such cases. Furthermore, you have already established for yourself that you cannot use dynamic SQL in a function. So there you are, seemingly stumped.
However, if you insist on using a single SELECT statement for this, there is one way – provided you agree to bend over backwards slightly to achieve the goal, that is. And accept a major limitation of the method.
The solution involves creation of a loopback linked server and using the OPENQUERY function. But first you will need to make sure your dynamic SQL solution works as it is. For the purpose of this answer, I am going to assume that the dynamic SQL looks like this:
DECLARE @sql nvarchar (max) = '', @sqltemplate nvarchar(max) =
'UNION ALL
SELECT
Column_Name = ''{Column_Name}'',
Table_Name = ''{Table_Name}'',
Max_Length = MAX(LEN([{Column_Name}]))
FROM
[oil stop].dbo.[{Table_Name}]
';
SELECT
@sql += REPLACE(
REPLACE(
@sqltemplate,
'{Column_Name}',
Column_Name
),
'{Table_Name}',
Table_Name
)
FROM
tempdb.dbo.YourMetaDataTable
;
SET @sql = STUFF(@sql, 1, 9, ''); -- remove the leading UNION ALL
EXECUTE sp_executesql @sql;
Once you have verified the script is working, and made sure the loopback linked server is created, just put the script inside the OPENQUERY function like this:
SELECT
*
FROM
OPENQUERY(
YourLinkedServerName,
'...' -- the dynamic SQL script
)
;
Remember to double each quotation mark (apostrophe) inside the script.
One other important change you will likely need to make is to add a WITH RESULT SETS clause to the EXECUTE statement to describe the result set, so that OPENQUERY can process the output correctly for you. When describing the result set, you will likely just repeat the same type for Column_Name and Table_Name as defined for them in the metadata table. For the example below I am assuming the type to be sysname in both cases. And as for the Max_Length column, I believe int would work well there. So, the modified EXECUTE statement would look like this:
EXECUTE sp_executesql @sql
WITH RESULT SETS
(
(Column_Name sysname, Table_Name sysname, Max_Length int)
);
For completeness, and to make the lack of elegance in this solution more evident for the wider audience, this is what the final query would look like:
SELECT
*
FROM
OPENQUERY(
[OIL STOP],
'DECLARE @sql nvarchar (max) = '''', @sqltemplate nvarchar(max) =
''UNION ALL
SELECT
Column_Name = ''''{Column_Name}'''',
Table_Name = ''''{Table_Name}'''',
Max_Length = MAX(LEN([{Column_Name}]))
FROM
[oil stop].dbo.[{Table_Name}]
'';
SELECT
@sql += REPLACE(
REPLACE(
@sqltemplate,
''{Column_Name}'',
Column_Name
),
''{Table_Name}'',
Table_Name
)
FROM
tempdb.dbo.MetaData
;
SET @sql = STUFF(@sql, 1, 9, ''''); -- remove the leading UNION ALL
EXECUTE sp_executesql @sql
WITH RESULT SETS
(
(Column_Name sysname, Table_Name sysname, Max_Length int)
);
'
)
;
The main problem, though, is that the query above still cannot be parametrised, and that is the principal limitation I was talking about. Even though the OPENQUERY script is specified as a string literal, it can only be a single string literal – not a variable, not a complex expression. That means that if you want to apply the query to a different subset of rows of the metadata table, you will have to use a new script for that.
Best Answer
I would use:
This gets the length of the field and subtracts 18 from it, leaving you with everything after the 18th character.