How can I search all tables and columns in a MS SQL 2000 database for a specific integer/string.?

Question

How can I search all tables and columns in a MS SQL 2000 database for a specific integer/string.?

Answer 1

opt for further FROM signup the place further=12/14/2006 INSERT INTO signup further VALUES '12/14/06' you additionally can specify a default fee for the column further so as that every time a checklist is inserted, that is going to immediately provide further the present date. Set Default fee to getdate() interior the column further. Use datetime for the column datatype, not smalldatetime.

Answer 2

You can search each table in the database by using the system stored procedure sp_MSforeachtable. In order to search through every column in every table you would need to write a cursor which uses the INFORMATION_SCHEMA.columns and INFORMATION_SCHEMA.tables system views to get the columns and tables lists. Then use dynamic SQL to search for the data you are looking for.

Answer 3

Open Query Analyzer.



Enter this SQL statement to search for a string:



SELECT * FROM tablename WHERE textcolumn LIKE '%searchterm%'



Enter this SQL statement to seach for an integer:



SELECT * FROM tablename WHERE integercolumn = integer



In the examples above:



tablename is the name of the table you are searching



textcolumn is the name of the column that contains a text (varchar, char, etc) data type



searchterm is the string you are trying to find



integercolumn is the name of an integer data type column you are searching



integer is the integer you are trying to find.