To search for a specified column in all tables in SQL Server 2005 _mssql2005

Sometimes we only know the name of the column, but do not know which table the data in the end, you can use the following method to find the table containing this data.

Copy Code code as follows:

Select o.name objectname, c.name ColumnName from sys.columns c inner join sys.objects O on c.object_id=o.object_id where C . Name like '%columnname% ' ORDER by O.name, C.name

In your actual trial, replace ColumnName with the name of the column you want to check.

How to write a search SQL statement when a string is in a field

Problem Description:

Often encountered this problem, we search for the condition that a string is contained in a field, and if a field is contained in a string, we know that we can use in,

such as sql= "select * Form TB where tb.name in (" & Names & ")"

Note: Name is a table TB field, name is the obtained string, and the query field name is in the given string names all the collections.

This approach naturally doesn't have to be said, but the problem we usually encounter is "reverse-coming", where the "name" string is in Tb.name table fields, such as the table TB Name field value: John, Dick, Harry, Zhao Liu

And we want to query the name= John, this must be judged when the table's name field contains the query criteria, that is, "John", that is, the query

Workaround:

1, the first thought of course is like query (not the ultimate solution, the ultimate way to continue to look down)

such as sql= "select * Form TB where tb.name like '%" & John & "%"

To explain this statement, you can query a collection of "John" in the Name field of all tables. However, if someone's name is "Qin Zhang 38", his name regardless of parents how to take, in short, there are "John" two words, so obviously also in line with the query, then he will naturally be queried, and we just " John ", don't" Qin Zhang 38 ", look next

2, imagine, if the value in the field is not "John, Dick, Harry, Zhao Liu", but ", John, Dick, Harry, Zhao Liu,"

What did you find out?

Yes, the string before and after a comma, which is useful, because the comma can be used as a limiting condition, if "Qin Zhang 38" At this time also in this field, his front and back also have commas, so that the comma to control the query, it is accurate in place

OK, rewrite the query into: sql= "select * Form TB where tb.name like '%," & John & ",% '"

Here, the principles and solutions are all said, but you may also encounter the following problems, continue to see the third step.

3, sometimes similar to this comma-like fields are we in the save time checked the value of the multiple select Check, when written to the database, will automatically be different check values after the comma,

For example, please choose your hobby, you may tick "read (value=1)", "Listen to Song (value=21)", "Climb (value=13)"

At this point the value is xingquaihao= "1, 21, 13", this field is saved, in the database is also the format, but at this time if the query directly according to the above query,

Direct use: sql= "select * Form TB where Xingquaihao like '%" & 1 & "%" will get all 1, even 21, 13 of 1, will also be detected

Add a comma to the field before and after it becomes ", 1, 21, 13," and if we look at 21 or 13, change to: sql= "select * Form TB where Xingquaihao like '%," & & ",% '" result is what You can't find out.

Why is there a value, but not to find out?

Because automatically check, and then save the middle of the field there are spaces, such as "1, 21, 13", 1 of the following is a comma, the comma followed by a space, then the 21

The solution is very simple, in the tick after the election, the storage before the space removed,

xingquaihao= "1, 21, 13"

Xingquaihao=replace (Xingquaihao, "", "") "Remove spaces

So get the value of the compact, and then follow the method above to query, fix!