How to Export data from SQL Server to Excel

Problem
Exporting data from SQL Server to Excel seems like a reasonably simple request. I just need to write out a few reports for users on a regular basis, nothing too fancy, the same basic report with a few different parameters. What native SQL Server options are available to do so? Do I need to learn another tool or can I use some T-SQL commands? Does SQL Server 2005 offer any new options to enhance this process?

Solution
Exporting data from SQL Server to Excel can be achieved in a variety of ways. Some of these options include Data Transformation Services (DTS), SQL Server Integration Services (SSIS) and Bulk Copy (BCP). Data Transformation Services (SQL Server 2000) and SQL Server Integration Services (SQL Server 2005) offers a GUI where widgets can be dragged and dropped Each option has advantages and disadvantages, but all can do the job. It is just a matter of your comfort level with the tools and the best solution to meet the need.

Another option that is available directly via the T-SQL language is the OPENROWSET command (SQL Server 2000 and SQL Server 2005). This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL. Below outlines the full syntax available:

OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } | BULK 'data_file' , { FORMATFILE = 'format_file_path' [ ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } ) ::= [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] [ , ERRORFILE = 'file_name' ] [ , FIRSTROW = first_row ] [ , LASTROW = last_row ] [ , MAXERRORS = maximum_errors ] [ , ROWS_PER_BATCH = rows_per_batch ]

Source - SQL Server 2005 Books Online

Below is a simple example of writing out the Job name and date to Sheet1 of an Excel spreadsheet in either SQL Server 2005 or 2000:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\testing.xls;', 'SELECT Name, Date FROM [Sheet1$]') SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs GO

Using the OPENROWSET command creates two caveats. The first caveat is the need to have an Excel spreadsheet serve as a template in the needed directory with the correct worksheets and columns. Without this the you would receive an error message. The second caveat is that it is necessary to enable the OPENROWSET command with the SQL Server 2005 Surface Area Configuration utility. Note - This step is not needed for SQL Server 2000. With the loop example you could copy and paste the Excel spreadsheet and load the data as needed.

Although the example above is very simple, you could integrate this simple example into your code. For example, if you had the need to write out a number of reports in the same format, you could loop (WHILE loop or Cursor) over the records and write them out to different Excel spreadsheets based on the name or report type. In addition, you could integrate this code with either SQL Server 2000 mail or Database mail (SQL Server 2005) and mail the results to the users with very little effort and build the process entirely with T-SQL.

SQL Server UDF to pad a string

Problem
Unlike other relational database management systems that shall remain nameless, SQL Server's underlying coding language, T/SQL, does not have a built-in function for padding string values. I recently took it upon myself to create my own and as you'll see I got a little carried away.

Solution
The task seems simple enough: create a user-defined function that will allow padding of a string value with a finite count of a desired character. What ended up as an experiment out of necessity became a slightly more-involved function once I decided that padding position should be customizable to meet the end user's needs.

While Transact-SQL (T/SQL) does not offer a comparable function similar to LPAD or RPAD available in other RDBMSs, the SQL Server Professional does have the REPLICATE() function that can be used to build a simple user-defined function that can be used to pad a string. Let's take a look at the REPLICATE() function and what it offers before moving on to the code for the custom padding function.

REPLICATE (string_expression ,integer_expression) will allow you to replicate a character string (the string_expression parameter the number of times consecutively per the integer_expression parameter).

A simple example of this function is presented below:

SELECT REPLICATE('ABCDE|', 3) ------------------ ABCDE|ABCDE|ABCDE| (1 row(s) affected)

Whereas the REPLICATE() function will allow you to return a string to a maximum size of 8000 bytes, the function I'll be creating will be based upon an output value of varchar(100). You will be able to modify this value to fit your needs, however I very rarely have a need to pad a string value greater than even 20 characters. I thought it worthwhile to create a single function for padding either to the left or right of the unpadded string. Then it became interesting, what if for some reason you wished to pad the center of the string? What about padding both the left and right sides of the string evenly? Whether you use those options or not, the functionality is there. The code below represents the function I've created.

CREATE FUNCTION [dbo].[usp_pad_string] ( @string_unpadded VARCHAR(100), @pad_char VARCHAR(1), @pad_count tinyint, @pad_pattern INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @string_padded VARCHAR(100) SELECT @string_padded = CASE @pad_pattern WHEN 0 THEN REPLICATE(@pad_char, @pad_count) + @string_unpadded --pad left WHEN 1 THEN @string_unpadded + REPLICATE(@pad_char, @pad_count) --pad right WHEN 2 THEN --pad center LEFT(@string_unpadded, FLOOR(LEN(@string_unpadded)/2)) + REPLICATE(@pad_char, @pad_count) + RIGHT(@string_unpadded, LEN(@string_unpadded) - FLOOR(LEN(@string_unpadded)/2)) WHEN 3 THEN --pad edges REPLICATE(@pad_char, FLOOR(@pad_count/2)) + @string_unpadded + REPLICATE(@pad_char, @pad_count - FLOOR(@pad_count/2)) END RETURN @string_padded END

The function expects four parameters:

• @string_unpadded - the raw string value you wish to pad.
• @pad_char - the single character to pad the raw string.
• @pad_count - the amount of times to repeat the padding character
• @pad_pattern - an integer value that determines where to insert the pad character
  • 0 - all pad characters placed left of the raw string value (pad left)
  • 1 - all pad characters placed right of the raw string value (pad right)
  • 2 - all pad characters placed at the midpoint of the raw string value (pad center)
  • 3 - the raw string value will be centered between the pad characters (pad ends)

Notes

• If either of the length of the supplied @pad_count or @string_unpadded values are odd, centering will be affected. We will show that behavior in the samples presented below.
• Since the return value of the function is limited to 100 characters, the length of parameter corresponding to the unpadded string must be sized according to allow for padding. The length of the return value and this parameter are completely customizable to fit your needs.
• While the REPLICATE() function allows you to pad more than a single character, this function expects you'll only pad a single character.

Example 1 Evenly-Distributable Padding

--Even distribution possible --Pad Left SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 0) AS [padded string], '0 - pad LEFT' AS [pad pattern value]; --Pad Right SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 1) AS [padded string], '1 - pad RIGHT' AS [pad pattern value]; --Pad Center SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 2) AS [padded string], '2 - pad CENTER' AS [pad pattern value]; --Pad Edges SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 4, 3) AS [padded string], '3 - pad EDGES' AS [pad pattern value];

The previous set of examples shows what results to expect when the parameters are evenly distributed. What happens though when this is not possible? This occurs under two situations: you are attempting to center-pad a raw string that has an odd number of characters or you're trying to pad an odd number of characters using the pad edges option (@pad_pattern = 3). I am not going to show you some method for splitting an odd string at a quantum level. Sorry, I am not all that brilliant as it is. Therefore I had to make a judgment call on how the behavior is going to occur using the structure of the three dimensions we have to work with here currently. The following examples will present how this function will behave.

Example 2 - Center-Pad an Odd-Length Raw String

--Pad Center SELECT '12345' AS [raw string], dbo.[usp_pad_string]('12345', 'X', 4, 2) AS [padded string], '2 - pad CENTER' AS [pad pattern value];

Example 3 - Center-Edges an Odd Number of Times

--Pad Edges SELECT '1234' AS [raw string], dbo.[usp_pad_string]('1234', 'X', 5, 3) AS [padded string], '3 - pad EDGES' AS [pad pattern value];

As you can see, the padding will be right-heavy in both cases. Though I doubt you'll have much use for center or edge-padding strings in Transact-SQL, you never really know.

Padding is a frequent need in returning string results to the end user of a RDBMS. This function allows you to do so as if the functionality existed inherently in T-SQL.

SQL Server Cursor Examples

Here's some Examples on SQL Server Cursor

Problem
In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft created them for a reason so they must have a place where they can be used in an efficient manner.

Solution
In some circles cursors are never used, in others they are a last resort and in other groups they are used regularly. In each of these camps they have different reasons for their stand on cursor usage. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor based processing is appropriate or not. To get started let's do the following:

• Look at an example cursor
• Break down the components of the cursor
• Provide additional cursor examples
• Analyze the pros and cons of cursor usage

Example Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

Cursor Components

Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

Additional Cursor Examples

In the example above backups are issued via a cursor, check out these other tips that leverage cursor based logic:

• Managing SQL Server Database Fragmentation
• SQL Server script to rebuild all indexes for all tables and all databases
• SQL Server Index Analysis Script for All Indexes on All Tables
• Standardize your SQL Server data with this text lookup and replace function
• Automating Transaction Log Backups for All SQL Server Databases
• Searching and finding a string value in all columns in a SQL Server table
• Scripting SQL Server Database Objects Using DMO (Distributed Management Objects)
• Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server
• Capacity Planning for SQL Server 2000 Database Storage
• Automate Restoration of Log Shipping Databases for Failover in SQL Server 2000
• Determining space used for each table in a SQL Server database
• Auditing Windows Groups from SQL Server
• SQL Server Find and Replace Values in All Tables and All Text Columns
• Easing the SQL Server Database Capacity Planning Burden
• Index Metadata and Statistics Update Date for SQL Server

Cursor Analysis

The analysis below is intended to serve as insight into various scenarios where cursor based logic may or may not be beneficial:

• Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
• Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor based approach was quick to develop and performed in an acceptable manner to meet the need.
• Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
• Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor based logic, but other system based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
• Large data sets - With large data sets you could run into any one or more of the following:
  • Cursor based logic may not scale to meet the processing needs.
  • With large set based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor based approach may meet the need.
  • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
  • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
  • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
  • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a checkpoint or marking each row with the cursor. However, with a set based approach that may not be the case until an entire set of data is completed. As such, troubleshooting the row with the problem may be more difficult.

Cursor Alternatives

Below outlines alternatives to cursor based logic which could meet the same needs:

• Set based logic
• SQL Server Integration Services (SSIS) or Data Transformation Services (SSIS)
• WHILE loop
• COALSCE
  • The Many Uses of Coalesce in SQL Server
• sp_MSforeachdb
  • Run The Same SQL Command Against All SQL Server Databases
• sp_MSforeachtable
  • Here is a tip with example code - http://www.mssqltips.com/tip.asp?tip=1274
• CASE expression
  • Using the CASE expression instead of dynamic SQL in SQL Server
• Repeat a batch with the GO command
  • Executing a T-SQL batch multiple times using GO

SQL Server user defined function to convert MSDB integer value to time value

Problem
In a recent tip I outlined a process for converting a date, stored as an integer into a datetime data type. Date and time information for run history of SQL Server Agent jobs is stored within the msdb..sysjobshistory table as an integer data type, not as a datetime as one would expect. Most likely for at least two reasons:

• This structure is a legacy implementation from the earliest days of SQL Server
• The values are stored in separate run_date and run_time columns and until SQL Server 2008 there was not a time data type per se

As promised, this tip picks up where we left off. On converting the integer-typed run_time into a format that is more user friendly for presentation purposes.

We will still be using the same metadata repository for the SQL Server instances I administer. From the previous tip, you may remember that one of the metrics I track is based upon Job History success and failure. This information comes directly from the msdb..sysjobhistory table that resides upon each SQL Server instance.

Solution
Let's take a look again at a simple query against the msdb..sysjobshistory and msdb..sysjobs tables that hold the data we're interested in:

sysJobHistory and sysJobs Data

SELECT SJ.name, SJH.run_date, SJH.run_time FROM msdb.dbo.sysjobhistory SJH INNER JOIN msdb.dbo.sysjobs SJ ON SJH.job_id = SJ.job_id WHERE SJH.step_id = 0 ORDER BY SJ.name GO

We have already covered how to convert run_date (as integer) to a datetime data type that can then be used in date calculations such as DATEADD(), DATEDIFF(), DATENAME() or DATEPART(). The purpose of this tip is to convert the run_time value (stored as an integer data type) into a format that is more presentable for end users. This can be accomplished in two manners, both outlined below. The later is a standalone user-defined function (UDF) the second takes into consideration an additional UDF outlined recently in a tip on padding string values. Reliance on this second UDF reduces the amount of code necessary. I'll be presenting the actual query execution plan for a simple query using each process so you can determine which option is the best for your environment.

As a point of reference, the msdb..sysjobhistory.run_time values are stored as an integer, in the pattern of hhmmss. Unfortunately for us, since this is an integer value, single digit values do not include a preceding zero (9:00 am for example is stored as 900, midnight as simply 0).

Option One - Standalone UDF

Let's take a look at our first option to address this problem which is a standalone UDF.

Option One: The Standalone UDF

CREATE FUNCTION dbo.udf_convert_int_time_1 (@time_in INT) RETURNS VARCHAR(8) AS BEGIN DECLARE @time_out VARCHAR(8) SELECT @time_out = CASE LEN(@time_in) WHEN 6 THEN LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 3,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) WHEN 5 THEN '0' + LEFT(CAST(@time_in AS VARCHAR(6)),1) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 2,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) WHEN 4 THEN '00' + ':' + LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) ELSE '00:00:00' --midnight END --AS converted_time RETURN @time_out END GO

The function accepts a single parameter, the integer data type time value passed to it. Depending on the length of the parameter, the output value is formatted accordingly and the result is returned. Revising the first query in this tip to include the results of this UDF applied to each record is presented below along with it's associated output and actual execution plan.

Sample Execution

SELECT SJ.[name], SJH.[run_date], SJH.[run_time], dbo.udf_convert_int_time_1(SJH.[run_time]) AS run_time_1 FROM msdb.dbo.[sysjobhistory] SJH INNER JOIN [msdb].dbo.[sysjobs] SJ ON SJH.[job_id] = SJ.[job_id] WHERE SJH.[step_id] = 0 ORDER BY SJ.[name] GO

Option Two: Reliance on Padding of Input Variable

Recently, I published a tip on MSSQLTips.com on padding string values in Microsoft SQL Server. We will be using the UDF presented in that tip to simplify the code presented above. Afterwards we'll see what possible effect that has on performance by comparing the actual execution plan against the execution plan for the first iteration of the UDF shown above.

Option Two: Reliance on Padding of Input Variable

CREATE FUNCTION dbo.udf_convert_int_time_1 (@time_in INT) RETURNS VARCHAR(8) AS BEGIN DECLARE @time_out VARCHAR(8) SELECT @time_out = CASE LEN(@time_in) WHEN 6 THEN LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 3,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) WHEN 5 THEN '0' + LEFT(CAST(@time_in AS VARCHAR(6)),1) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 2,2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) WHEN 4 THEN '00' + ':' + LEFT(CAST(@time_in AS VARCHAR(6)),2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2) ELSE '00:00:00' --midnight END --AS converted_time RETURN @time_out END GO

By utilizing the usp_pad_string() function we can eliminate the CASE code structure from the dbo.udf_convert_int_time_2 UDF. The padding function expects four parameters: the string value to pad, the padding character, the number of instances to apply the pad, and the padding placement. Please review the full structure of the usp_pad_string UDF in the original article. It will pad the integer value so that further processing can be consistently applied without concern for length. Running a comparable query to the one previously presented returns the following results and execution plan.

Sample Execution

SELECT SJ.[name], SJH.[run_date], SJH.[run_time], dbo.udf_convert_int_time_2(SJH.[run_time]) AS run_time_2 FROM msdb.dbo.[sysjobhistory] SJH INNER JOIN [msdb].dbo.[sysjobs] SJ ON SJH.[job_id] = SJ.[job_id] WHERE SJH.[step_id] = 0 ORDER BY SJ.[name] GO

The additional function call to the padding UDF has no apparent additional overhead on the execution of the query. Which option you choose would be up to you, dependent upon your preference for UDF reliance and embedding of UDFs. Ultimately this process is based upon conversion of time-of-day data into a presentable format. While the CONVERT() function is capable of converting string values to presentable formats when passed datetime values, there is no functionality for time-only values.

VERY IMPORTANT - BIG VIRUS COMING 'POSTCARD FROM BEJING'

Dear All,

PLEASE FORWARD THIS WARNING AMONG FRIENDS, FAMILY AND CONTACTS!

You should be alert during the next few days. Do not open any message with an attachment entitled 'POSTCARD FROM BEJING', regardless of who sent it to you. It is a virus which opens A POSTCARD IMAGE, which 'burns' the whole hard disc C of your computer.

This virus will be received from someone who has your e-mail address in his/her contact list. This is the reason why you need to send this e-mail to all your contacts. It is better to receive this message 25 times than to receive the virus and open it. If you receive a mail called' POSTCARD FROM BEJING,' even though sent to you by a friend, do not open it! Shut down your computer immediately.

This is the worst virus announced by CNN . It has been classified by Microsoft as the most destructive virus ever. This virus was discovered by McAfee yesterday, and there is no repair yet for this kind of virus. This virus simply destroys the Zero Sector of the Hard Disc, where the vital information is kept.

COPY THIS E-MAIL, AND SEND IT TO YOUR FRIENDS. REMEMBER: IF YOU SEND IT TO THEM, YOU WILL BENEFIT ALL OF US.

SQL Server Backup History Analysis

Problem

Database backups hold primary importance among daily DBA tasks. This task is typically automated through maintenance plans, scheduled SQL Server Agent Jobs or third party tools. With the importance of backups it is necessary to regularly analyze the performance and efficiency of the process. So how can we get insight into the performance of a backup process for any database?

Solution

Let's take a look at a few different scripts to see what sort of insight we can get. For the purposes of this tip, I have created a database by the name of 'BackupReport' to use in our examples. For testing purposes, full, differential and transaction log were performed to outline the value of the script. Check out the following script:

Script - Generate Backup Process Statistics

SELECT s.database_name, m.physical_device_name, cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize, CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(4))+' '+'Seconds' TimeTaken,s.backup_start_date, CASE s.[type] WHEN 'D' THEN 'Full'WHEN 'I' THEN 'Differential'WHEN 'L' THEN 'Transaction Log'END as BackupType, s.server_name, s.recovery_modelFROM msdb.dbo.backupset s inner join msdb.dbo.backupmediafamily mON s.media_set_id = m.media_set_idWHERE s.database_name = 'BackupReport'ORDER BY database_name, backup_start_date, backup_finish_date

Here are the results based on my example:
Now we have statistics for all of the backup processes for a given database. With this data we are able to analyze the changes in a specific time period or for different backup types.
Customizing the script
The script is for SQL Server 2005. If you are going to run this script on SQL Server 2000, the you have to remove the column 'recovery_model' from select list.
The script generates data for a specific database provided in the WHERE clause. If you want to generate statistics for all databases then simply modify the WHERE clause in the script above.
The script generates time taken in seconds. To get time in minutes or hours simply change the datediff parameter in the second line of the script to the required time unit.
The table 'backupset' in the 'msdb' database has additional information. Any of the following columns can be added to the SELECT statement of if additional information is required.
Column Name
Information
[name]
logical name of backup
[user_name]
user performing backup
[description]
description of backup
[first_lsn]
first log sequence number
[last_lsn]
last log sequence number
[checkpoint_lsn]
checkpoint log sequence number
[database_creation_date]
date of creation of database
[compatibility_level]
compatibility level of backed up database
[machine_name]
name of SQL Server where the backed originated
[is_password_protected]
either database backup is password protected or not
[is_readonly]
either database backup is read only or not
[is_damaged]
either database backup is damaged or not

Granting View Definition Permission to a User or Role

In SQL Server 2005 by default users of a database that are only in the public role can not see the definitions of an object while using sp_help, sp_helptext or the object_definition function. Sometimes this is helpful to allow developers or other non-administrators to see the object definitions in a database, so they can create like objects in a test or development database. Instead of granting higher level permissions, is there a way to allow users that only have public access the ability to see object definitions?


When issuing sp_help, sp_helptext or using the object_definition() function the following errors in SQL 2005 will occur if the user does not have permissions to the see the object metadata. Here are a couple of examples of these errors.

EXEC sp_help Customer
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'Customer' does not exist in database 'MSSQLTIPS' or is invalid for this operation.

A select against the OBJECT_DEFINITION function will return a value of NULL if the user does not have permissions to see the meta data.

SELECT object_definition (OBJECT_ID(N'dbo.vCustomer'))
NULL

By default users were able to see object definitions in SQL Server 2000, but in SQL Server 2005 this functionality was removed to allow another layer of security. By using a new feature called VIEW DEFINITION it is possible to allow users that only have public access the ability to see object definitions.

To turn on this feature across the board for all databases and all users you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

To turn on this feature across the board for all databases for user "User1" you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO User1

To turn this feature on for a database and for all users that have public access you can issue the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO PUBLIC

If you want to grant access to only user "User1" of the database you can do the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO User1

To turn off this functionality you would issue the REVOKE command such as one of the following:

USE master
GO
REVOKE VIEW ANY DEFINITION TO User1

-- or

USE AdventureWorks
GO
REVOKE VIEW Definition TO User1


If you want to see which users have this access you can issue the following in the database.

USE AdventureWorks
GO
sp_helprotect

Here are two rows that show where the VIEW DEFINITION action has been granted. The first on a particular object and the second for all objects in the database.





To take this a step further, if you do not want to grant this permission on all objects the following stored procedure can be used to grant this to all objects or particular objects in a database. This is currently setup for all object types, but this can be changed by including less object types in the WHERE clause.

WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U')
/*
Included Object Types are:
P - Stored Procedure
V - View
FN - SQL scalar-function
TR - Trigger
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/


To use this, you can create this stored procedure in your user databases and then grant the permissions to the appropriate user instead of making things wide open for a user or all users. Just replace ChangeToYourDatabaseName for your database before creating.

USE ChangeToYourDatabaseName
GO
CREATE PROCEDURE usp_ExecGrantViewDefinition
(@login VARCHAR(30))
AS
/*
Included Object Types are:
P - Stored Procedure
V - View
FN - SQL scalar-function
TR - Trigger
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/
SET NOCOUNT ON

CREATE TABLE #runSQL
(runSQL VARCHAR(2000) NOT NULL)

--Declare @execSQL varchar(2000), @login varchar(30), @space char (1), @TO char (2)
DECLARE @execSQL VARCHAR(2000), @space CHAR (1), @TO CHAR (2)

SET @to = 'TO'
SET @execSQL = 'Grant View Definition ON '
SET @login = REPLACE(REPLACE (@login, '[', ''), ']', '')
SET @login = '[' + @login + ']'
SET @space = ' '

INSERT INTO #runSQL
SELECT @execSQL + schema_name(schema_id) + '.' + [name] + @space + @TO + @space + @login
FROM sys.all_objects s
WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U')
AND is_ms_shipped = 0
ORDER BY s.type, s.name

SET @execSQL = ''

Execute_SQL:

SET ROWCOUNT 1

SELECT @execSQL = runSQL FROM #runSQL

PRINT @execSQL --Comment out if you don't want to see the output

EXEC (@execSQL)

DELETE FROM #runSQL WHERE runSQL = @execSQL

IF EXISTS (SELECT * FROM #runSQL)
GOTO Execute_SQL

SET ROWCOUNT 0

DROP TABLE #runSQL

GO

Once this procedure has been created you can grant the permissions as follows. This example grants view definition to a user "userXYZ" in "MSSQLTIPS" Database for all object types that were selected.

USE MSSQLTIPS
GO
EXEC usp_ExecGrantViewDefinition 'userXYZ'
GO

Next Steps