You can increase the size of Results to Text output in SSMS settings, but that still won't be enough.
#SQLPRO SEARCH FOR COULMN FULL#
PRINT sys.sp_executeSQL on the number of searchable columns in your system, PRINT won't necessarily show you the full command, and you might think there is a bug in the code (or at least a bug in PRINT) that somehow truncates the text.
SELECT N' OR ' + QUOTENAME(name) + N' LIKE ' + CASEįROM sys.columns AS c WHERE c. + char(39) + columns + N' FROM ' + obj_name + N' WHERE ' SELECT obj_name = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name), WITH tables(obj_name, obj_id, columns) AS DECLARE nvarchar(255) = nvarchar(128) = nvarchar(max) = N''
What it does do slightly differently is that it returns a single resultset for each table, not for every single column, so the output is only one row per match no matter how many columns match. Supports text/ ntext (though you shouldn't be using those anymore), char/ nchar/ varchar/ nvarchar, and even puts the leading N on the search string where appropriate. Here is a solution that, like approach, figures out from metadata which columns are eligible for string search. ' WHERE ' + + ' LIKE ' + ColumnName, ColumnValue FROM #Results I modified this stored proc to take a table name as the second parameter and just search that table for the data: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'.') AND type in (N'P', N'PC'))ĬREATE PROC nvarchar(100) = nvarchar(256) = 'dbo.Alerts'ĬREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))ĪND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')ĪND QUOTENAME(COLUMN_NAME) > IS NOT NULL
0 kommentar(er)
