sql-servert-sqlsp-msforeachtable

SQL Server sp_msforeachtable

I am having a problem with using the sp_msforeachtable SQL Server stored procedure.

I want to have output of EXECUTE statement in one table using following SQL query:

TRUNCATE TABLE DaneOSpuscie --clear the table

EXEC sp_msforeachtable 
    'INSERT INTO [DaneOSpuscie] ([Database name], [Table name])
     SELECT  ''?'', name
     FROM [sys].[columns] 
     WHERE [sys].[columns].[name] LIKE ''%[Tt][aA][pP12]%'' '

This throws an error

String or binary data would be truncated

for each table.

Additional info: Table "DaneOSpuscie" has only two columns: Database name, Table name of type varchar.

Solution

  • Check your table structure. Check your max length of your objects.

    Compare it. Set your columns in your table to real max length or use maximal system object length - sysname = nvarchar(128).