Encontar un valor en varias columnas en sql

Solo disponible en BuenasTareas
  • Páginas : 2 (351 palabras )
  • Descarga(s) : 0
  • Publicado : 3 de marzo de 2011
Leer documento completo
Vista previa del texto
CCREATE PROCEDURE [dbo].[FindValue] @TableName NVARCHAR(128), /* Must be a valid table or view name, must not be quoted or contain a schema*/ @ValueNVARCHAR(4000), /*May contain wildcards*/ @schema NVARCHAR(128) = 'dbo' /*May be left out*/ AS /* Sample Execution Exec FindValue @TableName = 'spt_monitor', @Value = '8', @schema = 'dbo' */ /* Ifgiven a string it will finds all rows where any char, varchar, or their Unicode equivalent which contain that string in the selected table or view. Note that this only works on objects which haveentries in information_schema.columns, which excludes certain system objects. If given a numeric value it will check those text types for a match as well as numeric types. If given a possible date,it will also check date type. The string that is being searched for may contain wildcard characters such as %. This will NOT search text, ntext, xml, or user defined fields. This may return arow more than once if the search string is found in more than one column in that row. */ /**************************** Declare Variables ***********************/ DECLARE @columns TABLE (ColumnNameNVARCHAR(128)) DECLARE @sql NVARCHAR(MAX) /************************** Populate Table Variable *****************/ /*Takes the names of string type columns for the selected table */ INSERT INTO @columns(ColumnName) SELECT Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_schema = @schema AND Table_name = @TableName AND data_type IN ('char', 'nchar', 'varchar', 'nvarchar')/* If it is numeric, also check the numeric fields */ IF ISNUMERIC(@value) = 1 INSERT INTO @columns (ColumnName) SELECT Column_name FROMINFORMATION_SCHEMA.COLUMNS WHERE Table_schema = @schema AND Table_name = @TableName AND data_type IN ('int', 'numeric', 'bigint', 'money', 'smallint', 'smallmoney',...
tracking img