Reading View Definitions in SQL Server

Security changes tend to break existing functionality. True, such changes are often required, but poor users and developers simply want their programs to continue to work.

Case in point: In SQL Server (2005 and later), if you can SELECT from a view, or invoke a function or stored procedures, you do not automatically have the privileges to read the T-SQL definition of these objects.

MSDN states:

In earlier versions of SQL Server, metadata for all objects in a database is visible to members of the public role. This means that any user […] can view metadata for every object in the server, even those objects on which a user has no rights.

In SQL Server 2005 and later, the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission.

The consequence is that while you can query sys.objects for the view, you do not automatically get the OBJECT_DEFINITION() of the view.

What are the basic rules?

  • If you are db_owner, you see everything.
  • If you are db_datareader, you can select sys.objects (tables, views, table-valued functions), but OBJECT_DEFINITION returns NULL.
  • To gain access to object definitions (sometimes referred to as catalog views, or metadata) otherwise, you need to have the View Definitions grant assigned:

What are the consequences?

Create users with different database roles assigned, and run these statements when connected as these users:

FROM sys.objects
WHERE type IN ('V', 'P', 'FN')

Depending on the user privileges will either receive no data at all, or the object names and a NULL definition, or objects and their T-SQL definition.

As dbscript implements a metadata import for SQL Server databases, it needs the definition of a connection string for each database.

While there is a Test Connection button to check the validity of the connection parameters, it does not yet check whether the Metadata is actually accessible. This functionality will be implemented in an upcoming version of dbscript.

Limitations of the T-SQL PRINT Command

One of the main functions of dbscript is to allow the user to select a set of tables and columns. This selection is used to generate T-SQL code (either a set of statements or the definition of a stored procedure) which can then be executed on a live database to generate INSERT/UPDATE statements reproducing the values of the selected tables and columns. An earlier post illustrated this functionality.

While this works in principle (at least for MS SQL Server), there are restrictions in the T-SQL command that make live harder than necessary if you want to script long text or binary values (NVARCHAR(MAX), VARBINARY(MAX):

During research I found a couple of work-arounds for the 4000/8000 byte limit:

  • stored procedure LongPrint on ask.sqlservercentral
  • stored procedure LongPrint on Adam Anderson’s blog

Both procedures try to find a line break in the string to be output, split the string at the line breaks and prints the substrings accordingly. However, from looking at the code, the procedures only work correctly if there is a line break found within 4000 characters, which is a problem if

  • you have a list of column values without line breaks (in case of dbscript: INSERT and UPDATE values)
  • you want to output binary values whose hexadecimal representation is longer than 4000 characters

With all these restrictions in place, I considered some other solutions:

The sp_executesql solution is necessary because scripts should be generated without any requirements in the target databases. The idea is to encapsulate a version of LongPrint in a TSQL variable, and invoke this code using sp_executesql and passing the string to be output.

Preparing this blog I came to the solution that the current implementation is good enough for most cases. In cases where output length exceeds certain limits, or for databases that enforce similar limits or do not support outputting text at all, a generic C# implementation in the dbscript backend may be the best solution.

To be continued… 😉

dbscript 1.04 released

The code for the latest version of dbscript has been sitting around for a while, and I did not find the time to release it until a recent bug report forced encouraged me to finally publish it. What’s more, I promised to provide an update!

So what’s new in dbscript 1.04?

Architectural Cleanup

Up to dbscript 1.03, every new supported database engine and every new object type stored their values in either base columns or object type-specific columns. The object type-specific columns are now mapped onto the same columns in the Object table (using some meta data tables, and an internal code generator which also creates the NHibernate classes).

New functionality

Dependency Diagrams (based on T-SQL analysis of views, stored procedures and functions)

namespace parameter for C# and VB.Net value scripts

Internal Cleanup

Recognize sql server 2008 and 2008R2

Multi-comparison is performed in C# rather than TSQL, reducing the chance of a TSQL timeout, and allows adding/removing project versions after a calculation has been performed.

XmlSchemaCollections are treated as types instead of objects

UI Cleanup

A couple of UI inconsistences have been fixed

Compare Object HTML-escapes correctly

Administrators can delete projects

MediaWiki login

dbscript user Mark made pointed out that dbscript does not work with current versions of MediaWiki anymore. A bit of research found that the login form adds a new hidden parameter wpLoginToken which needs to be submitted in the login POST.

dbscript 1.04 has been modified to provide this parameter, and hopefully this fix solves the issue for everybody having problems generating MediaWiki documentation of their databases.

As always, the latest version of dbscript is available for download here.

Database Changes Done Right

I love reading The Daily WTF dealing with programmers’ stories, experiences, and the occasional code nightmare. While it’s fun to read, it also makes you aware of how easily you can mistakes yourself, especially in an unfamiliar programming language, even after years of programming experience.

A recent article was titled Database Changes Done Right, and I was surprised that it was not fun or horror story, but rather a serious article on database change management, along with a couple of rules to watch when implementing database schema changes (yes, those ugly dev – test – production stages).

I also felt connected with the topic of the article, because for a couple of years now, I develop software that should help other developers ease this burden of managing database changes:

dbscript (blog, website, download) is a web application that stores database versions in repository (i.e. MSSQL database), calculates schema change scripts, stores definitions of table value initialization scripts and C# constant declarations, and keeps track of product version at customer installations. dbscript focuses on MS SQL Server databases in these areas, but also supports Oracle and PostgreSql databases for documentation purposes: document your database schema in a wiki or in a Word HTML file, or generate a data diagram with just a couple of mouse clicks.

SMOscript (blog, website, download) is a command-line tool that generates SQL scripts for all objects in a MSSQL database (tables, views, stored procedures, etc) to a single file, or to one file per object. These generates scripts can be kept in directories covered by source control systems (SVN, TFS, etc), and thus SMOscript simplifies storing the scripts for each database version in a source code repository.

oraddlscript (blog, website, download) is a command-line tool similar to SMOscript, but covering Oracle databases.

checktsql (blog, website, download) is a Windows tool that verifies all objects in a database (views, functions, stored procedures) by executing them or selecting from them (each in a transaction that will be rolled back), and reporting any errors that occurred during their invocation.

This product summary does not cover the complete functionality of each program. As programs evolve, they gain a lot of functionality, as users (including me!) add requirements and contribute ideas. Feel free to add yours!

Database Dependency Diagrams

Preparing the release of the next version of dbscript, I can proudly present you one of its new functions: to generate a Dependency Diagram of all objects contained in a database:

dependency diagram of AdventureWorks 2008 (png)

dependency diagram of AdventureWorks 2008 (dia screenshot)As usual, the diagrams can be generated in .png and .dia format.

Dependency analysis and dependency diagram generation are currently only implemented for MS SQL Server databases.

dbscript 1.03 released

dbscript, the integrated database versioning, documentation and code generation web application, has finally been migrated to .Net 3.5 (SP 1).

This makes it easier to install, as the separate installation of ASP.Net Ajax libraries is not necessary anymore.

The draw-back is that it is no longer possible to run the web application on Windows 2000 (Server), if that is a concern to anybody.

Generated Documentation

MS SQL Server Oracle PostgreSql edmx
dbscript HTML view view view
dbscript Single HTML view view view view
HTML (single file) view view view view
MediaWiki view view view view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view view view view

dbscript is available for download here.

Integrated Database Versioning and Documentation with dbscript 1.02

dbscript 1.02 adds support for edmx-based projects. If have previously written about generating documentation out of edmx files, and these documentation generators are now part of dbscript.

This is the updated table of Documentation Generators in dbscript 1.02:

Generated Documentation

MS SQL Server Oracle PostgreSql edmx
dbscript HTML view view view
dbscript Single HTML view view view view
HTML (single file) view view view view
MediaWiki view view view view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view view view view

The XSLT files on which the Documentation Generators depend for their functionality are now computed by a separate tool I wrote for this purpose, making the generated documentation more consistent throughout the different formats and databases.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Other features

  • Data diagrams can now be restricted to a certain Object Selection resulting in a diagram showing only a part of the full model.
  • Update notifications are implemented via JSONP
  • Some fixes

dbscript is available for download here.

Generating Documentation of Entity Framework edmx Files

The upcoming version 1.02 of dbscript supports uploading, versioning and documenting Entity Framework Model Files (extension .edmx).

I have described earlier how to write XSLT files to convert the contents of edmx files into MediaWiki markup.

Follow these links to view sample output generated by dbscript:

T-SQL Syntax Error due to Compatibility Level

My application dbscript queries the system catalog views to retrieve a database schema, and uses different SELECT statements for SQL Server 2000 and for 2005 and higher. To my surprise, it recently raised a syntax error when reading from a 2008 database:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘sys’.

The statements typically look like this

SELECT    sys.xml_schema_collections.xml_collection_id, as schema_name,, ep.value as Description
FROM    sys.xml_schema_collections
INNER JOIN sys.schemas
  ON sys.schemas.schema_id = sys.xml_schema_collections.schema_id
OUTER APPLY fn_listextendedproperty('MS_Description',
  'XML SCHEMA COLLECTION',, null, null) ep
WHERE    xml_collection_id > 1

Note the OUTER APPLY invocation of the table-valued function fn_listextendedproperty().

A quick search led me to this thread suggesting that the compatibility level of the database may not be the most current (100 for SQL Server 2008). I checked, and indeed compatibility level was 80 as a result of restoring a SQL Server 2000 database. Setting the compatibility level to 100 fixed the problem.

Personally, I find the compatibility level a bit inconsistent (see descriptions for sp_dbcmptlevel and ALTER DATABASE).

T-SQL 2000 simply did not have an OUTER APPLY, nor a fn_listextendedproperty() (without preceding ::), nor the sys schema.

Its description

Sets certain database behaviors to be compatible with the specified version of SQL Server

does not mention APPLYs or table-valued functions. And it’s not clear why exactly a parameter to a TVF is causing the syntax error. I guess that’s outside the scope of “certain behaviors”.

Lesson learned: when restoring a database from a different server version, check the compatibility levels 😉

Here’s the statement to do so:

select name, compatibility_level
from sys.databases

Source Control Management and Databases

Source controlling or versioning database schemas is a recurring topic on StackOverflow and on database-related blogs and web sites.

There does not seem to be a single correct solution, rather everybody tries to find their own way to deal with this topic.

The core of the problems seems to be the different types of data between file-based source code and database schemas. Whereas source control systems typically have to deal with source code in files, and every check-in relies on the ability to compile every revision in source control (well, in an ideal world), versioning databases is different in the aspects:

  • store a version of the full database schema (for installation)
  • store a change script / migration script to upgrade from one version to the next (for upgrades)
  • store the values of certain tables (lookup tables, system and application constants)

Command-line tools

In recent years I developed a set of tools that I think may be helpful to the task of versioning databases:

SMOscript is a tool which scripts all database objects in an MS SQL Server database to file using the MS SMO library. Using the -f switch, one file per object is created in a directory. If that directory is under source control, the developer has all CREATE statements in the same revision tool as their application source code.

Oraddlscript is the equivalent of SMOscript for Oracle databases. It makes use of the built-in function DBMS_METADATA.GET_DDL, which provides the CREATE statements for any object inside an Oracle database.

Integrated solution

dbscript is a web application which tries to integrate the 3 aspects mention above:

The generated files can be stored inside the dbscript database (always related to a schema version or a comparison between versions), and / or downloaded to a directory covered by source control.

Note: I am biased, since I use dbscript for the projects I develop since its first stable and reliable version. Actually, dbscript development itself uses dbscript, as can be seen in the SQL files shipped for installation and upgrades.

For more information on the products, see the company website. Free downloads are availabe here.