I would like to have all the tables having one column called XXX
I've tried the following query but it's not working.
SELECT object_id
FROM sys.columns
WHERE name = 'XXX';
Asked
Active
Viewed 3,554 times
2
-
3"it's not working" - what does it mean? – Hamlet Hakobyan Mar 18 '14 at 15:32
-
@HamletHakobyan: pressing F5 is aking me to create a report file – Revious Mar 18 '14 at 15:34
-
"having one column called XXX" - is that "among the columns in those tables, they have a column called XXX" or "these tables have exactly one column, and it's named XXX"? – Damien_The_Unbeliever Mar 18 '14 at 15:34
-
2@Revious open a brand new query window and try again. It sounds like you've clicked on the "Results to File" button (CTRL+SHIFT+F). – gvee Mar 18 '14 at 15:35
-
@gvee: you are rigth, solved. – Revious Mar 18 '14 at 15:36
-
1@Revious sorted. Updated my answer. – gvee Mar 18 '14 at 15:39
2 Answers
6
SELECT table_schema
, table_name
, column_name
FROM information_schema.columns
WHERE column_name LIKE '%test%'
You've mentioned in your question comments that "it's not working" =
pressing F5 is aking me to create a report file
In Management Studio you have a few options for what to do with your query results:
- Results to Text
CTRL + T - Results to Grid (default)
CTRL + D - Results to File
CTRL + SHIFT + F
Sounds like you've hit the "Results to File" button (or key combination).
gvee
- 16,732
- 35
- 50
2
Here's one that will grab both the column and the table it belongs to:
SELECT DISTINCT so.name AS tablename, sc.name AS columnname
FROM syscolumns sc
INNER JOIN sysobjects so
ON so.id=sc.id
WHERE sc.name LIKE '%XXX%'
ORDER BY so.name, sc.name
Bensonius
- 1,501
- 1
- 15
- 39