Finding System Objects in SQL Server Databases

Occasionally, mistakes were made and user database objects, such as tables, stored procedures, and functions are created in master database by accident. Now the question is how to identify the system database objects and the user database objects?

In Microsoft SQL Server Management Studio, on the Object Explorer, if you expand the database tree and then the database object tree, for example tables, you can see a folder called System Tables (for Tables), System Views (for Views), etc..

You can also query the sys.objects. There is a column in there called is_ms_shipped. It is a bit flag to identify if the “object is created by internal SQL Server component”. Please see this article to get more information on the sys.objects. So to see which objects are created by internal SQL Server components (system objects) in master database, you can run the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE is_ms_shipped = 1;

Alternatively, you can also use the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 1;

Conversely, if you want to find user database objects in the master database, you can run the following query:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE is_ms_shipped = 0;

Or:

  1: USE [master];
  2: 
  3: SELECT name,
  4:      object_id,
  5:      type_desc,
  6:      create_date,
  7:      modify_date,
  8:      is_ms_shipped
  9: FROM sys.objects
 10: WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 0;

There is a couple caveat to this. Kalen Delaney (blog | twitter), pointed out in her blog post, that there is an undocumented stored procedure sp_MS_marksystemobject that can marked a user created system database object into system objects. Also if we turned on the allow updates configuration in master database, then any stored procedures that the user created in the master database will be marked as 1 for the IsMSShipped property.

Downloading SQL Server 2012 CTP 3

During the first day of SQL PASS Summit 2011, Ted Kummert, Corporate Vice President of Business Platform Division, revealed that the next generation of SQL Server code name Denali is going to be SQL Server 2012. It is slated to be release during the first half of 2012.

Currently Microsoft has released the Community Technology Preview (CTP) 3 of Denali, or shall I say SQL Server 2012. SQL Server 2012 CTP 3 has been released for awhile. But I have not got the chance to get it … until now. I’ve decided to try it out.

So I created a virtual environment using VMWare player. For the operating system, I decided to use Microsoft Windows Server 2008 R2 Standard edition (64-bit). Once I got that setup, I tried to download SQL Server 2012 CTP 3 from Microsoft site. You can find the installation files/ISO (for both the 32-bit and 64-bit version) on the following URL:

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

When I tried to download it from the guest environment (Microsoft Windows Server 2008 R2 Standard edition 64-bit) using Internet Explorer 8 and 9, I run into several problems. The first problem that I ran into was that, when I click the download button, nothing seems to happen. The second problem was that I keep getting the following message:

an add-on for this website failed to run

Solutions

There are a couple of ways in which you can resolve this:

IE Enhanced Security Configuration (ESC)

By default, on Windows Server 2008 IE Enhanced Security Configuration is enabled (I believe we also have this setting on Windows Server 2003). This setting is basically to prevent attacks on your computer from untrusted websites. So basically it is not allowing IE to run scripts.

You can turn this setting off. When you do so, you should be able to download SQL Server 2012 CTP 3 without any problem. To turn this setting off, you can go to Server Manager. Under the Security Information section, you should see a link for Configure IE ESC. Click on that one, and you should see a window similar to below:



Here you can turn on/off the IE Enhanced Security Configuration (ESC). This might not be ideal, since basically you are turning off that extra protection.

Trusted Sites

Alternatively, you can add a couple of sites to your Trusted Sites setting. To do this, you can do the following:

• Open Internet Explorer
• Go to Tools – Internet Options.
• On Internet Options window, click on the Security tab.
• Click on the Trusted sites, and then the Sites button.
• On the Add this website to the zone, type in *.microsoft.com (uncheck the Require server verification (https) for all sites in this zone), then click on Add button.
• On the Add this website to the zone, type in *.akamai.com (uncheck the Require server verification (https) for all sites in this zone), then click on Add button.
• Click on the Close button on the Trusted sites window.
• Click on the Ok button.

Now you should be able to download the SQL Server 2012 CTP without any problem. So basically on this one, you are saying to Internet Explorer that you are trusting Microsoft and Akamai website, and depending on the security level that you have for trusted sites (normally it is Medium-High), you are allowing scripts to run. You might ask, why we need to trust Akamai site? The Microsoft site is pretty obvious, since the download page is located on Microsoft site. But then Microsoft use Akamai download manager.

24 Hours of PASS: Fall 2011

Professional Association for SQL Server (PASS) is going to have two days of SQL Server online training on September 7 – 8, 2011. The training event is called 24 Hours of PASS. Just as its name indicates, there are going to be 24 training sessions, with each running for about an hour long. The sessions will be conducted by SQL experts, such as Brent Ozar, Gail Shaw, Denny Cherry, and many others. The event is going to covers many aspects of SQL Server, such as SQL Server Business Intelligence, PowerShell for SQL Server Administration, SQL Server Integration Services, and many more. You can see the list of sessions that they are going to have here.

While the training will not make you an expert in the subject, but it is a great way to quickly expose yourself to the subject. The thing about this event is that it is free. You can sign up for as many sessions as you like. To register for the sessions, you can go here.

So, to quickly recap:

What: 24 Hours of PASS (24HOP)
Where: Virtual
When: September 7 – 8, 2011

For more information, you can click here. To register you can click here.

SQL Server Export to Excel 2007

The ability to export SQL Server data into Excel is very useful. Often time, I need to extract data from SQL Server for users and for most part they want it in Excel format since the data can be manipulated, analyzed and re-formatted very easily.

In Microsoft Excel 2003, each worksheet can only have a maximum of 64,000 rows. However, in Microsoft Excel 2007, we can have a maximum of 1,000,000 rows on each worksheet. Because of this, in some cases, I need to do data export from SQL Server to Microsoft Excel 2007. The problem is that I don’t have Microsoft Excel 2007 installed on my development system. I only have Microsoft Excel 2003. Now the question is that can I use SQL Server data export feature to export data from SQL Server to Excel 2007 without having Microsoft Excel 2007 installed in my system? The answer to that as I found out is yes (but with a few caveats).

On my system, I have SQL Server 2008 Management Studio Express (you can get this free from here), and a blank Microsoft Excel 2007 xlsx file (that I copy from another system that have Microsoft Excel 2007 installed).

Now if I tried to export data from SQL Server to Excel 2007 using SQL Server 2008 Management Studio Express, I would get the following error message:

The operation could not be completed.

Additional information:

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)

To resolve this error, I just need to install Microsoft Excel 2007 Viewer. Microsoft Excel 2007 Viewer is free and can be downloaded from here.

Once I installed Microsoft Excel 2007 Viewer, I am able to use the data export feature on Microsoft SQL Server 2008 Management Studio to export data from SQL Server into Microsoft Excel 2007 without any problem.

Table Valued Parameters

There is one new feature in SQL Server 2008 that I am pretty excited about, that is the ability to pass table valued parameters onto stored procedures or functions. One scenario where this could be handy is when we have to insert multiple rows of data. For example, let say you have an application where you allow users to input multiple new employee information at once and you need to insert all those information into the employee table. Prior to SQL Server 2008, we probably would loop through each row (employee information) and call the stored procedure that insert the information to the employee table one row at the time. Alternatively we could parse those employees information into XML and pass the XML into a stored procedure where it will then process the XML and insert the information to the employee table. But now with SQL Server 2008, we could pass the new employees information as a table valued parameters into a stored procedure, which can make things simpler.

You might ask why not use bulk insert or bcp. According to Microsoft, in many cases, if the number of rows is less than 1,000, using table valued parameters actually might be better performance wise.

How to Use Table Valued Parameters

It is pretty simple actually, You just need to do the following:

1. Create a user defined table type. This actually defined the structure for the table valued parameters.
2. Create the stored procedure or user defined function that would utilized the user defined table type (created on Step 1) as its parameter.
3. You can now pass table valued parameters to the stored procedure or user defined functions created on Step 2.

Example

Let say that I have an table called EmpInfo. I created the table using the following SQL Script:

CREATE TABLE dbo.EmpInfo (
    EmpID INT IDENTITY(1,1) PRIMARY KEY,
    EmpFirstName VARCHAR(25),
    EmpLastName VARCHAR(25),
    EmpTitle VARCHAR(25),
    CreatedDate    DATETIME);

Then I created a user defined table type called EmpInfoTableType using the following SQL Script:

CREATE TYPE dbo.EmpInfoTableType AS TABLE (
    EmpFirstName VARCHAR(25),
    EmpLastName VARCHAR(25),
    EmpTitle VARCHAR(25));

I now can create a stored procedure that utilized EmpInfoTableType user defined table type as one of its input parameter by using the following SQL Script:

CREATE PROCEDURE dbo.InsertEmpInfoBatch (@EmpInfoBatch EmpInfoTableType READONLY)
AS
BEGIN
    INSERT INTO EmpInfo (EmpFirstName, EmpLastName, EmpTitle, CreatedDate)
    SELECT EmpFirstName, EmpLastName, EmpTitle, CURRENT_TIMESTAMP
    FROM @EmpInfoBatch
END
GO

If you notice there is READONLY keyword when declaring the EmpInfoBatch parameter (table valued parameter). This is required for table valued parameters (or more of a restriction actually), as we can not do any data modification to the table valued parameters within the stored procedure and user defined function.

Now the fun part, we test it:

DECLARE @EmpInfoTable AS EmpInfoTableType; 

INSERT INTO @EmpInfoTable 
VALUES ('John', 'Doe', 'CEO'),
('Jane', 'Smith', 'CFO'),
('Mike', 'Fox', 'CTO') 

EXEC dbo.InsertEmpInfoBatch @EmpInfoTable;

If we issue the following SQL Script:

SELECT EmpID, EmpFirstName, EmpLastName, EmpTitle, CreatedDate
FROM dbo.EmpInfo

We should get the following result:

Pretty simple, eh?