Is there a way to retrieve the view definition from a SQL Server using plain ADO?

by

Which version of SQL Server?

For SQL Server 2005 and later, you can obtain the SQL script used to create the view like this:

select definition
from sys.objects     o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( 'dbo.MyView')
  and o.type="V"

This returns a single row containing the script used to create/alter the view.

Other columns in the table tell about about options in place at the time the view was compiled.

Caveats

  • If the view was last modified with ALTER VIEW, then the script will be an ALTER VIEW statement rather than a CREATE VIEW statement.

  • The script reflects the name as it was created. The only time it gets updated is if you execute ALTER VIEW, or drop and recreate the view with CREATE VIEW. If the view has been renamed (e.g., via sp_rename) or ownership has been transferred to a different schema, the script you get back will reflect the original CREATE/ALTER VIEW statement: it will not reflect the objects current name.

  • Some tools truncate the output. For example, the MS-SQL command line tool sqlcmd.exe truncates the data at 255 chars. You can pass the parameter -y N to get the result with N chars.

More Related Contents:

  1. How do I use cascade delete with SQL Server?
  2. How can foreign key constraints be temporarily disabled using T-SQL?
  3. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  4. How to add Active Directory user group as login in SQL Server
  5. Maximum size of a varchar(max) variable
  6. How to update large table with millions of rows in SQL Server?
  7. How to Use UTF-8 Collation in SQL Server database?
  8. How do you specify a different port number in SQL Management Studio?
  9. Export table to file with column headers (column names) using the bcp utility and SQL Server 2008
  10. Sequence as default value for a column
  11. Aggregate bitwise-OR in a subquery
  12. What does “ORDER BY (SELECT NULL)” mean?
  13. Parse XML in SQL Server
  14. Calling stored procedure using VBA
  15. SQL Unique constraint across multiple tables
  16. How to find SQL Server running port?
  17. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  18. Changing SQL Server collation to case insensitive from case sensitive?
  19. SQLServer IDENTITY Column with text
  20. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  21. Why SQL Server DATETIME type saves time in ticks of 1/300 of a sec?
  22. How to store directory / hierarchy / tree structure in the database?
  23. Cannot bulk load. Operating system error code 5 (Access is denied.)
  24. INFORMATION_SCHEMA vs sysobjects
  25. Merge multiple rows into a single row
  26. Does query plan optimizer works well with joined/filtered table-valued functions?
  27. SQL Server equivalent to Oracle’s CREATE OR REPLACE VIEW
  28. Conditional JOIN Statement SQL Server
  29. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  30. Oracle sequence but then in MS SQL Server

Leave a Comment