Find all tables given column name

Question

I'm using a piece of code (via Excel VBA) which seems to be in several spots around the internet. The intention is to find all tables which contain a certain field/column.

This is my SQL string:

SELECT t.name AS table_name, 
       SCHEMA_NAME(schema_id) AS schema_name, 
       c.name AS column_name FROM sys.tables AS t 
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
WHERE c.name LIKE 'MyCol' 
ORDER BY schema_name, table_name;

This works perfectly for most tables, but not views. Is there a way to look within views as well?

If anyone has any ideas, I'd really appreciate feedback. I hope this all makes sense as I am fairly new to SQL, but I have been doing VBA for a long time. Thanks so much in advance!

Be aware that this sql is ripe for SQL injection attacks. Use parameterized sql queries instead. — crthompson, May 05 '14 at 19:57
@paqogomez Isn't it a bit overparanoid? it's an admin script, how could it be prone to injection attack if it's not being exposed to outside world? — dean, May 05 '14 at 20:03
@dean I see what you are saying but I agree with @paqogomez. OP did not state the usage for this script and passing in a `%` would in essence preform a complete schema dump of the database. Which can create immense security issues depending on what information the server stores. It is better to write all queries in a secure way than to get in the habit of using direct injection inside a string. — engineersmnky, May 05 '14 at 20:06
Sorry, I am a little lost about the injection attacks. I'll try to do some reading about that and the meaning of parameterized sql queries. Thank you for your replies. — The Kurgan, May 05 '14 at 20:07
@engineersmnky Do you really believe this script will be exposed to an end user so he could enter the parameters? — dean, May 05 '14 at 20:10
@dean, its not all about whether or not a person in the position WOULD do it. Its about writing responsible code. [Never trust data from your users](http://msdn.microsoft.com/en-us/library/ee798441(v=cs.20).aspx) is a wise and time tested adage. — crthompson, May 05 '14 at 20:11
@paqogomez Sure, but it's not a production code, it's an admin script, there is a world of difference! What do you want me to do, exec all my admin scripts with sp_executesql? Really? — dean, May 05 '14 at 20:15
@dean given the fact that it is being run from Excel VBA (User Facing Application) it does not seem impossible that this is the intention. From the concept of the post it seems that a `User` will be looking up this information. Even if that user is an administrator allowing them to dump a full schema seems like a bad idea and as @paqogomez stated it is about writing proper code. what happens if I ran a full schema dump and then decided to lookup a column using a `DELETE` statement. Wouldn't it seem silly when this could have been prevented with a few extra steps? — engineersmnky, May 05 '14 at 20:18
@dean OP has changed this sql, but as originally written it concatenated the text from `DB_Data.TextBox1.Text`. Parameterized SQL is [very](http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection) [well](http://stackoverflow.com/questions/601300/what-is-sql-injection) [documented](http://stackoverflow.com/questions/tagged/sql-injection), and in this case, `sp_executesql` doesnt even factor in to the idea. — crthompson, May 05 '14 at 20:19
@dean, you also seem to be in the know about what is and isnt production code though OP's question makes no mention of it. How do you know that its not? — crthompson, May 05 '14 at 20:21
@paqogomez Queries on system catalogs are usualy not part of production code, don't you think? And there's really no need to try teach me about injection or parameterization, pls :) — dean, May 05 '14 at 20:24
@dean, I never make assumptions about what people want to put in production. I think your definition of production and mine are different anyway. As far as teaching goes, I like LittleBobbyTable's [note here](http://stackoverflow.com/a/23401109/2589202). SO is about promoting good questions and answers and pushing out poor ideas. Your idea was poor. — crthompson, May 05 '14 at 20:31
I'm not exactly following this conversation, but let me explain what I do. I write processes in Excel/Access for end users to gather data. I have been strictly using Access as my method of linking tables and then querying off those. Lately, however, I've found that it's much cleaner and faster to write SQL code which pulls data directly and thereby bypassing Access altogether. The end users and I only have read-only access to the data and I always lock-down my VBA code with passwords. — The Kurgan, May 05 '14 at 20:31
I'm not sure if this helps clarify anything, but I do want to read more about being responsible, as I don't want to inadvertently write something that will cause a crash or compromise data. — The Kurgan, May 05 '14 at 20:32
@user3605606, I think we're off into more theory than actually answering your specific question. :) You are well intentioned, check out those links I posted. As to why its bad, [start with XKCD. :)](http://xkcd.com/327/) — crthompson, May 05 '14 at 20:33
And @xQbert should post his answer so he can get the reps. ;) — crthompson, May 05 '14 at 20:34
@user3605606 Don't worry, jou're doing just fine here. You only need to worry about the injection if you're taking parameters entered by end users and concatenating it into queries. This is a real threat but not in this particular case. Not everything has to be parameterized and sanitized. — dean, May 05 '14 at 20:53

score 2 · Accepted Answer · answered May 05 '14 at 20:46

Use sysobjects instead of systables

sysobjects is most DML based objects (except triggers) whereas systables is just well... tables

http://technet.microsoft.com/en-us/library/ms190324.aspx

and becareful of sys.objects vs sysobjects.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a965676e-d4d9-4365-ad0a-58ca26ec4701/differenece-between-sysobjects-and-sysobjects-in-sql-server-2005-?forum=sqlkjmanageability

Find all tables given column name

1 Answers1