SQL to find all instances of columnname.tablename

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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL to find all instances of columnname.tablename

Author

Topic

thebrenda
Starting Member

22 Posts

Posted - 2013-08-05 : 12:05:36

Let us say that a table names was changed from allkeys.table1 to key1.table1. For a given database, how can i find all references to allkeys.table1?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-05 : 12:18:14

[CODE]
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name = 'allkeys' or table_name = 'key1'
[/CODE]

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-05 : 12:20:14

EDITED:

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name = 'allkeys' and column_name = 'table1'

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-06 : 16:02:56

Look at:
sys.sql_expression_dependencies

If you need further assistance with that view, let me know.

Btw, INFORMATION_SCHEMA views are not reliable in SQL Server, and so should not be used.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-08-07 : 11:25:52

quote:
Originally posted by ScottPletcher

Look at:
sys.sql_expression_dependencies

If you need further assistance with that view, let me know.

Btw, INFORMATION_SCHEMA views are not reliable in SQL Server, and so should not be used.

Really? Any documentation to back that up? If so, then what should be used instead?

EDIT: Researched, found. Thanks for pointing this out.
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 11:37:01

I also recall seeing some MSDN pages where they have the beloved exclamation marks and warn something to the effect of "Don't use INFORMATION_SCHEMA views to get the schema of this object". Looked for it now, but couldn't find.

If I am not mistaken, aren't INFORMATION_SCHEMA views supposed to be the ANSI compliant way of doing things? Proponents of portable code would be livid over Microsoft ignoring the INFORMATION_SCHEMA views!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-08-07 : 12:10:43

quote:
Originally posted by DonAtWork
Then what should be used instead [of INFORMATION_SCHEMA views]?

Use MS's "sys.<view_name>" system views. Yes, not ANSI standard. But nothing that is ANSI standard that works correctly is available.

Besides, there's so little portability between SQL variants that you can't really share code anyway. For example, date functions vary dramatically among SQL, Oracle and UDB (IBM).

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 11:46:08

You can use the following query:

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_name = 'allkeys' and column_name = 'table1'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources