Search code examples
sql-servert-sqlms-accessadox

How can I access a view command through T-SQL?

I'd like to be able to retrieve programmatically the command strings that generate the views on our SQL Server.

I though the ADOX collection, used together with an ADODB connection, will allow us to access this through the catalog/view/command property. Unfortunately, the 'views' collection is not available when connecting from an MS-Access client to a SQL Server through an ADO connection, which is our case (see Cannot Use ADOX Views Collection with SQL Server).

I hope I could now find a T-SQL alternative to this problem. I will then be able to send the T-SQL instruction through my ADO connection, and collect the corresponding text string on my client side.

Solution

  • Something like this?

    SELECT
    v.name,
    m.definition
FROM 
    sys.views v
INNER JOIN 
    sys.sql_modules m ON v.object_ID = m.object_id

    Marc