-1

I have access to a set of views that are built on a database in SQL. What I want to do is search for a term e.g. "Florida" across all columns in all of the views and produce a table that has the columns as per the image attached:

enter image description here

Community
  • 1
  • 1
CodeMeARiver
  • 79
  • 1
  • 8
  • What have you already tried? I'd suggest looking at something like a cursor to iterate across all of the Views you want to check, then some dynamic SQL to query each one and produce the results you want. – 3N1GM4 Oct 11 '18 at 11:23

1 Answers1

0

Use this to generate you a bunch of SQL statements:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(
    'SELECT "{s}" as [SchemaName], "{v}" as [View Name], "{c}" as [Column Name] FROM [{v}] v WHERE v.[{c}] LIKE "Florida%";'
    , '{s}', c.TABLE_SCHEMA)
    , '{v}', c.TABLE_NAME)
    , '{c}', c.COLUMN_NAME)
    , '"', '''')
FROM information_schema.columns c INNER JOIN sys.views v ON v.name = c.TABLE_NAME

Then copy them out of the results grid, and run them in the query pane

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Hi Caius, I tried your code, but it didnt quite work. It gave me the following error: "Incorrect syntax near 'SELECT "{s}" as [SchemaName], "{v}" as [View Name], "{c}" as [Collumn Name] FROM [{v}] v WHERE v.[{c}] LIKE "Florida%";'." – CodeMeARiver Oct 15 '18 at 16:06
  • Missing bracket in the sql, try the revised version – Caius Jard Oct 15 '18 at 18:54