Searching fields in database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Searching fields in database

Author

Topic

Informer30
Starting Member

26 Posts

Posted - 2011-06-08 : 10:11:08

Hi All,I am using SQL 2008 R2 and I have a table with a lookup field. The lookup field must connect to another table and rather than go through the 100+ tables is there a search facility which can show me which other tables hold this field? Or a sql script?Many Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-08 : 10:21:37

You can search information_schema.columns if you are looking for the column name.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Informer30
Starting Member

26 Posts

Posted - 2011-06-08 : 10:55:03

Thanks Nigel,

information_schema.columns is that a table or view?

BTW, iam new to scripting....

Many Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-08 : 11:09:39

[code]SELECT object_name(object_id) as [table]
FROM sys.columns
WHERE name = 'column name here';[/code]

Informer30
Starting Member

26 Posts

Posted - 2011-06-08 : 11:54:38

Thanks Nigel...

Informer30
Starting Member

26 Posts

Posted - 2011-06-09 : 04:13:10

Hi,
please can you advise :

information_schema.columns

Is that a view or table where can I find this in the db? Or is it something that is default to all dbs?

Many Thanks

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-06-09 : 05:03:39

information_schema.* are system views that you can use to query various parts of a database, i.e all table names, all column names, etc. It's actually extremely handy if you want to do the same operations on multiple tables for example or compare table schemas etc. Run this script in your database to see an example (the script doesn't do anything to your database, just run it...you'll see what I mean):

SELECT 'SELECT TOP 10 * FROM ' + table_name FROM INFORMATION_SCHEMA.TABLES

-- or this -->

DECLARE @table varchar(100)
DECLARE @cols varchar(4000)
SET @table = 'table_name'
SET @cols = 'SELECT TOP 10 '
SELECT @cols = @cols + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table
SET @cols = LEFT(@cols, LEN(@cols)-1) + ' FROM ' + @table
PRINT @cols

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Informer30
Starting Member

26 Posts

Posted - 2011-06-09 : 05:45:21

Thanks for infor...but when I try running these scripts I am getting a message 'Unable to start the TSQL DEBUGGER. Does not support SQL sERVER 2005 OR EARLIER VERSIONS.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-09 : 08:14:09

How are you running it?
Sounds like you are trying to debug rather than execute.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Informer30
Starting Member

26 Posts

Posted - 2011-06-09 : 08:25:44

running it in Management Studio....

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-09 : 08:50:27

Make sure you are hitting EXECUTE(the ! icon) and not RUN (the green arrow icon).

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources