116

I'm successfully extracting column definitions from databases hosted on a SQL server using the ADO Connection OpenSchema() call in its various incarnations so I can programmatically recreate those tables in another SQL database. So far, so good.

The main interaction with the above tables happens using multiple views; while OpenSchema() is able to return the column definitions for the view in the same way that it returns column definitions for a table, a crucial bit of information is missing - which table and column in the underlying tables the column in the view maps to.

I tried to access the SQL command used to create the view using ADOX Catalog Views, but it appears that the OLEDB driver for SQL Server that we're using doesn't support this functionality.

Is there any way to get at this information for the view configuration via ADO, either in a way that states "ColumnX maps to ColumnY in table Z" or in the form of the actual SQL command used to create the view?

Braiam
  • 1
  • 11
  • 47
  • 78
Timo Geusch
  • 24,095
  • 5
  • 52
  • 70

7 Answers7

206

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.

schlamar
  • 9,238
  • 3
  • 38
  • 76
Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135
  • In SQL 2000, (using `syscomments` instead of `sys.sql_modules`), this seems to always return `CREATE VIEW`, regardless of whether it was last modified with `ALTER VIEW`. The altered version is still returned, just with "create" at the beginning. – Nathan Nov 28 '12 at 22:52
  • 11
    SQL query from the answer can be simplified a little bit: `select m.definition from sys.sql_modules m where m.object_id = object_id('dbo.MyView', 'V')` – Ivan Nov 03 '13 at 11:33
  • 14
    another caveat is you probably need the right permissions to be able to view the definition. I am getting NULL for them. – rveach May 06 '15 at 21:17
  • For me, this returns only the first 255 chars (or something like that) and not the complete definition... – schlamar Jun 01 '15 at 08:46
  • 1
    @schlamar, if all you're seeing is the first 255 characters, you're converting the result column incorrectly. The schema for `sys.sql_modules` defines the column thus: `definition nvarchar(max) SQL text that defines this module. NULL = Encrypted.` – Nicholas Carey Jun 01 '15 at 16:45
  • 1
    @schlamar, you might also note that if you're using SSMS/Query Analyzer, if you're running query results out as *text* (rather than a grid), by default, `[n][var]char` data is truncated at 256 characters. You can change that via the menu at `Query..Query Options...` — From the ensuing modal dialog, drill down to the `Results>Text` node in the tree control on the left-hand side. – Nicholas Carey Jun 01 '15 at 17:10
  • 1
    I use the MS-SQL command line tool (sqlcmd.exe). It truncates the data, too. I had to pass the parameter -y N to get more data (really MS?). So thanks for pointing me in the right direction. – schlamar Jun 02 '15 at 07:23
  • Just curious, if a view contains a line with the comment started '--' this will comment out the whole remainder of the view def. Is there anyway to get this line by line. I dont know why SS's data dictionary is so cumbersome – Ab Bennett Oct 31 '17 at 06:22
  • Late to the party... an even more simplified version is `select definition from sys.views v join sys.sql_modules m on m.object_id = v.object_id` – Gerry Coll Mar 19 '19 at 21:51
  • For @AbBennett and anyone else experiencing the problem where comments mess up the returned SQL, try this https://stackoverflow.com/a/1862297/2205882 – user16973 Jan 10 '21 at 22:55
33

Microsoft listed the following methods for getting the a View definition: http://technet.microsoft.com/en-us/library/ms175067.aspx

USE AdventureWorks2012;
GO
SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.vEmployee'); 
GO

USE AdventureWorks2012; 
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('HumanResources.vEmployee')) 
AS ObjectDefinition; 
GO

EXEC sp_helptext 'HumanResources.vEmployee';
DaveAlger
  • 2,376
  • 25
  • 24
15

For users of SQL 2000, the actual command that will provide this information is:

select c.text
from sysobjects     o
join syscomments    c on c.id = o.id
where o.name = '<view_name_here>'
  and o.type      = 'V'
TravelinGuy
  • 323
  • 3
  • 6
  • This version returns the View broken into multiple records of 4,000 characters each. (Tested in SQL Server 2014.) – Ben Jan 13 '20 at 18:23
11
SELECT object_definition (OBJECT_ID(N'dbo.vEmployee'))
Remi Guan
  • 21,506
  • 17
  • 64
  • 87
STiAT
  • 119
  • 1
  • 3
3

You can get table/view details through below query.

For table :sp_help table_name For View :sp_help view_name

vkstream
  • 881
  • 8
  • 8
2
SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound  
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('your View Name');
Bosco Han
  • 103
  • 5
0

This example:Views Collection, CommandText Property Example (VB) Shows how to use ADOX to maintain VIEWS by changing COMMAND related to VIEW. But instead using it like this:

 Set cmd = cat.Views("AllCustomers").Command  

' Update the CommandText of the command.  
cmd.CommandText = _  
"Select CustomerId, CompanyName, ContactName From Customers"

just try to use this way:

Set CommandText = cat.Views("AllCustomers").Command.CommandText
Michał Lipok
  • 365
  • 6
  • 24