Interview Question of the Week #002 – Script to Find Byte Size of a Row for All the Tables in Database

In recent times I was part of the interview panel where I was stunned to see that following question was asked to interviewee. I personally believed that this question is no longer relevant as we are in the age where database are capable to handle large amount of data and the row size is no more significant. However, the question was still asked and the interviewee has to still answer it with regards to the latest version of SQL Server. Here it goes:

Question: How to find total row size in bytes for any table in SQL Server?

Answer: In SQL Server 2000 and earlier version it was crucial to know that byte size of the row as there was limit to how big a row can be. This limit has been removed in the recent versions of SQL Server so this question is no more relevant now.

Here is the script for recent versions of SQL Server. I have an additional column which indicates that if a table contains any column with datatype MAX.

ELSE ISNULL(, 'unknown') END AS Table_name,
SUM(scol.max_length) AS Approx_Max_Length,
CASE WHEN MIN(scol.max_length) = -1 THEN 'Yes'
ELSE 'No' END AS Fieldwith_MAX_Datatype
FROM sys.objects sob
INNER JOIN sys.columns scol ON scol.OBJECT_ID=sob.OBJECT_ID
WITH CUBE          

If you are using SQL Server 2000, here is the blog post which contains the answer SQL SERVER – Query to Find ByteSize of All the Tables in Database.

Reference: Pinal Dave (

SQL SERVER – What is the query used in sp_cursorfetch and FETCH API_CURSOR?

In a recent debugging exercise for performance, one of my friend recently posted a question to me:

“When I view activity monitor, under the recent expensive queries and under query column, there I see a “fetch api_cursor0000000000000003”. What is this? How can I know what was running behind this query?

Obviously, the hint from that question was the keywords “Fetch and Cursor”. So I knew what the starting point would be. How many times you have been into situation where you want to track a query but when you capture profiler or use conventional methods of sys.sysprocesses or DBCC INPUTBUFFER you would see something like this:

The same thing in profiler we would look like below:

EXEC sp_cursorfetch 180150003,16,8,1


FETCH API_CURSOR0000000000000001

Note: the number in the query might differ in your environment though. Here is a VBScript code to simulate the problem.

Dim strConnection
Dim MyConnection
Dim MyRecordSet
Dim strSQL
strConnection = “Provider=SQLOLEDB;Data Source=.\SQL2014;Initial Catalog=Master;Integrated Security=SSPI;”
Set MyConnection = CreateObject(“ADODB.Connection”)
MyConnection.Open strConnection
Set MyRecordSet = CreateObject(“ADODB.recordset”)
strSQL = “SELECT TOP 3 * FROM sysobjects”
MyRecordSet.Cursorlocation = 2
MyRecordSet.Open strSQL, MyConnection, 3,3
Set MyRecordSet = Nothing

To reproduce the issue, you can save above code in a file and keep extension .vbs. Then you can run from command prompt. You may need to change value of Source in the connection string to match your environment. Once we run the VB Script, you would get a popup with the table name. If profiler is capture from the beginning, we should see sp_cursoropen which can tell the query. If query is already running then we can use below query

SELECT creation_time,
SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
CASE c.statement_end_offset
ELSE c.statement_end_offset
END - c.statement_start_offset) / 2) + 1) AS statement_text
FROM   sys.Dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st

Here is the sample execution and as we can see, we can get query text which has opened cursor.


Note: The cursor_id is same as what we saw in sp_cursorfetch . Additionally, if we capture complete profile, we can see the below output:

Moral of the story: If you are seeing sp_cursorfetch or FETCH API_CURSOR0000000000000001 then either capture complete profiler, since the starting of the query OR use sys.Dm_exec_cursors to get exact query.

Do let me know if you every faced this problem in your environments before. How did you debug the same?

Reference: Pinal Dave (

SQL SERVER – What is is_not_trusted in sys.foreign_keys?

My greatest source of learning is via my blog. Many of you take time in asking your toughest questions and sometimes basic questions that make me think. This interaction creates a source of exploration for me and ultimately translates into my knowledge sharing. All questions asked by DBAs are not straight forward and sometimes I also have to do some research before providing answer to them. Here is another interesting question that I received via email recently -

Hi Pinal, What is the meaning of is_not_trusted column in sys.foreign_keys?

I would have told to check books online but if we look at books online for sys.foreign_keys, the column says “FOREIGN KEY constraint has not been verified by the system.” This is not very clear and easy to understand. No wonder he asked me this questions. Let us learn this using an example:

For simplicity let us assume we have three tables: EmpMaster, AddressMaster and EmpAddress using the below schema:

CREATE TABLE AddressMaster(AddressId INT PRIMARY KEY, Address VARCHAR(100), Status CHAR(1))
CREATE TABLE EmpAddress(Emp_id INT, AddressId INT)

From the name you might have guessed that EmpAddress is the mapping table which can have only those values which are in master tables. So, to enforce referential integrity we should create foreign keys to avoid violation of parent-child relationship.

FK_EmpLocation_EmpMaster FOREIGN KEY
) REFERENCES dbo.EmpMaster
FK_EmpLocation_AddressMaster FOREIGN KEY
) REFERENCES dbo.AddressMaster

We have set the basic structure to work. Now, let us insert one row in each master table as below.

INSERT INTO EmpMaster VALUES (1, 'Pinal','Dave')
INSERT INTO AddressMaster VALUES (1, 'Bangalore, India', 'A')

Now, if we try to insert a value (1, 2) in child table, we would get error

INSERT INTO EmpAddress (Emp_id, AddressId) VALUES (1, 2);

Msg 547, Level 16, State 0, Line 33

The INSERT statement conflicted with the FOREIGN KEY constraint “FK_EmpLocation_AddressMaster”. The conflict occurred in database “SQLAuthDB”, table “dbo.AddressMaster”, column ‘AddressId’.

The statement has been terminated.

This error is expected because “2” is not a valid value for AddressId because it’s not available in parent table i.e. AddressMaster.

Let’s look at metadata for foreign key in the catalog view sys.foreign_keys

SELECT name, is_not_trusted FROM sys.foreign_keys

Is_not_trusted is set to zero by default which means that child is not having data which is not in parent. Now, let’s use below to disable the constraint.

ALTER TABLE [dbo].[EmpAddress] NOCHECK CONSTRAINT [FK_EmpLocation_AddressMaster]

And now insert the value.

INSERT INTO EmpAddress (Emp_id,AddressId) VALUES (1, 2)

This means, now we have inconsistent value in the child table. Executing the query on sys.foreign_keys again will confirm this assumption:

SELECT name, is_not_trusted FROM sys.foreign_keys

As highlighted above, the value 1 in the column means that system has not verified the consistency of the data. Now, let’s try to enable the constraint again.

ALTER TABLE [dbo].[EmpAddress] WITH CHECK CHECK CONSTRAINT [FK_EmpLocation_AddressMaster]

You might say that above looks incorrect but “Check” twice is intended. That’s the correct syntax to enable the constraint. Here is the error one would receive while enabling it.

Msg 547, Level 16, State 0, Line 59

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_EmpLocation_AddressMaster”. The conflict occurred in database “SQLAuthDB”, table “dbo.AddressMaster”, column ‘AddressId’.

Modify the value as below.

UPDATE EmpAddress
SET AddressId = 1
WHERE AddressId = 2

And now we can re-execute the ALTER TABLE command and it should work.

Once you are done with the above script, you can clean up the database by executing following script:


So this was a quick overview of the is_not_trusted value in the foreign_keys table and how it functions. Do let me know if you have every used this feature anywhere.

Reference: Pinal Dave (

SQL SERVER – Iterating over Metadata with BimlScript – Notes from the Field #061

[Note from Pinal]: This is a 36th episode of Notes from the Field series. One of the common question I receive every other day is – I have learned BI but I feel that there is no automation in the BI field, what should I do? How do I go beyond learning BI? How can I fill the gap between BIDS and SSDT? If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains what is Biml and How Can it Help Me with SQL Server BI?. Read the experience of Reeves in his own words.

In previous articles (What is Biml and The Why of Biml), I’ve discussed Business Intelligence Markup Language (Biml) and given some examples of projects that would benefit from its use. I also demonstrated a quick way to get introduced to Biml without learning a lot of C#. As you grow in your understanding of Biml and BimlScript you will want to make your Biml files more dynamic with a programmatic metadata-driven approach. This article will build on the previous articles and add looping with metadata to build more dynamic Biml projects.


Starting where the previous article left off, I’ll demonstrate how to add a little C# to make a more automated method of metadata retrieval. If you did not walk through the other articles, I would suggest that you review them before you read this one. This article builds on the Biml in Listing 1 and lets you create multiple integration services packages from one Biml file.

Listing 1 – Semi-Automated Common Source Package Biml

Example – Automated Source Packages

This example shows you how to connect to a database and select the metadata to iterate over. Then you can create multiple SQL Server Integration Services (SSIS) packages from one Biml file. The example in the previous article manually updated the metadata and reran each Biml file. This is an effective approach, but you can build on it to create a more robust solution. Take a look at the C# code in Listing 2.

Listing 2 – C# Replacement Code

Replace the first line of code from Listing 1 with the code in Listing 2, which is an excerpt from Listing 3.

  • The first line will add the namespace to the Biml file and enable the calling of functions without a fully qualified name.
  • The second line creates a string variable that contains a connection string of the database with the metadata.
  • The third line creates a connection object (actually an AstOleDbConnection node) that will be used by the ImportDB function.
  • The forth line creates a result set with the metadata from the connection object. This object contains all of the metadata from the AdventureWorks2012 database. The ImportDB function has three parameter. The first parameter is a schema filter, and in the example the filter is on the Person schema. The second parameter is a table filter and is blank in the example, so it will be ignored. The last parameter is a list of ImportOptions, and the views have been excluded. To add more import options use the “|” followed by additional options. Example of multiple options:

ImportOptions.ExcludeForeignKey | ImportOptionsExcludeViews

The example includes an object that contains all of the metadata that you will loop over. You have to place the loop within the <Packages> node because a Biml file can only have one Packages collection. The foreach loop will create multiple <Package> nodes.

A tableName string was built below the foreach loop to reduce the code that you have to alter from the example in the previous article.

Listing 3 – Complete Code

Note: The code in listing 3 has the ConnectionString truncated to save space.

If you receive the error shown in Figure 1, it means that the tables do not exist in the stage database. To check this, add the Person filter to the table parameter in the ImportDB function and return metadata for only one table. The prior articles used this table, so it should exist.

Figure 1 – Error Message


This article added some more C# to help you refine your Biml files. I hope you can start to see the power of Biml and how it can help remove the repetitive work from your next Integration Services project.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (

SQL SERVER – Identify and Filter In-Memory Optimized Tables – SQL in Sixty Seconds #079

Earlier I had written a blog about SQL SERVER – Beginning In-Memory OLTP with Sample Example which covers the basics of working with In-Memory OLTP. Though that post gets you started, one of my colleague asked me if there was an easier way to identify In-Memory Tables when working with SQL Server Management Studio. As a follow up I wrote another blog post over here where I demonstrate the same with images and query over here: SQL SERVER – Filter In-Memory OLTP Tables in SSMS.

I have converted the same blog post in SQL in Sixty Seconds video over here.

Let me know your opinion about it.

Here are few other blog posts related to this concept, which I have written earlier.

Reference: Pinal Dave (

SQL SERVER – Schema Change Reports – SQL in Sixty Seconds #078

Earlier, I wrote a blog post about Schema Change Reports and Finding Tables Created Last Week – DBA Tip. I received quite a few emails about the same. It seems like a quite a popular topic. As per few requests I have created a very short 60 second video on this subject.

Let me know your opinion about it.

Here are few other blog posts related to this concepts, which I have written earlier.

Reference: Pinal Dave (

SQL SERVER – Finding Tables Created Last Week – DBA Tip

My conversations with my DBA friends are always interesting. They have unique ways to solve problems and organizations take them for granted. Most of the DBA’s want to be in control of their environment. There is no second thought on it when it comes to delivery from their side.

In a recent conversation, one of my friend asked me if there was a way to identify the tables that were created in the database in the past one week. He was asked by his security team to bring a report on when an object is created in the production server. They want to make sure no unnecessary objects are getting created in the database without prior approval.

Though my thought process was to build an audit for this, I thought of sending a simple script that can be used to identify the same.

SELECT AS [Object_Name], [Schema_Name],
o.type_desc [Description],
o.create_date [Creation_Date],
o.modify_date [Modified_Date]
FROM   sys.all_objects o
LEFT OUTER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE  create_date > (GETDATE() - 7) OR modify_date > (GETDATE() - 7)

A sample output is shown below:

As you can see, the query can be added to a SQL Server job and we can send the same as an email to the DBA or security team automatically. The other option I gave my friend was to run an SSRS report and schedule a report output via email to the Security team.

If you are person not doing such hi-fi stuff of sending reports, then there can be a much easier way to find this information. We can use the “Schema Change History” report available inside SQL Server Management Studio to get this information.

A typical output is shown above. This is similar to the query output we discussed before but in a built-in report that is out-of-box. Most of these SQL Server Management Studio tips are simple and are hidden in some shape or form.

Do let me know if you every have had the need for such requirements? What did you do in that case? Would love to hear your experience and implementation for sure.

Reference: Pinal Dave (