0

Apologies for posing the question incorrectly earlier, but the recommended "already answered" question wasn't what I was looking for.

So I'm using the following:

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbName.viewName')

But I'm not getting the information I thought I'd get. For example, in this create view:

CREATE VIEW [dbo].[vSample]
AS
SELECT dbo.[Docu].docID
FROM dbo.AcctK INNER JOIN
     dbo.[Docu] ON dbo.AcctK.docID = doc.[Docu].docID
WHERE (dbo.AcctK.ix = 3) AND (dbo.AcctK.val IN ('SAMPLE1', 'SAMPLE2',
      'SAMPLE3', 'SAMPLE4', 'SAMPLE5'))
GO

I want to retrieve, with a query, all of the SAMPLE strings (i.e., SAMPLE1, SAMPLE2...)

Gmac
  • 169
  • 2
  • 14
  • possible duplicate of [Is there a way to retrieve the view definition from a SQL Server using plain ADO?](http://stackoverflow.com/questions/4765323/is-there-a-way-to-retrieve-the-view-definition-from-a-sql-server-using-plain-ado) – mwigdahl May 13 '15 at 13:13

1 Answers1

0

The characters

dbName.viewName

doesn't mean anything to the SQL compiler. The parameter passed to the function must be a string, in one of these forms:

<objectName>
<schemaName>.<objectName>
<databaseName>.<schemaName>.<objectName>

for example,

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.viewName')

To mention, this form

SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('MyDatabase.dbo.viewName')

is supposed to work, but for whatever reason it did not work for me just now in SQL 2008.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • @Phillip Kelley, thank you for that, it did work but did not produce what I was hoping for. – Gmac May 13 '15 at 14:04