SQL SERVER – Finding Tables Created Last Week – DBA Tip Part II

When I wrote my previous blog post on SQL SERVER – Finding Tables Created Last Week – DBA Tip, lesser did I know something as simple as this can become such an interesting topic of conversation. I have been overwhelmed with the number of emails I have got in the past week to know more about such real life scenarios. Having said that, a number of you also asked are there a much easier way to look at the same inside SQL Server Management Studio.

As I was thinking about possible options, onething that struck me is – I havent told you how this can be done using UI inside SQL Server Management Studio.

SSMS is a powerful tool and a number of options exisits to address the same scenario. Let me talk about two other easier ways to get the answers to our question next.

Object Explorer

When using Object Explorer, get to Tables node -> Right Click and Select Filter -> Filter Settings. Once here, you will get a “Creation Date” Property which can be used ot filter. Select the Operator and the value and we are all done.

This will filter the “Tables” node on Object Explorer with objects which were created based on the date condition we specified in the filter.

Object Explorer Details Pane

The next option is to use the Object Explorer Details Pane (F7 shortcut). Here on the header, select the “Create Date” Column and then sort by the same. Now the tables that were created latest will bubble to the top.

Awesome right? These are couple more ways to find the latest tables that were created in the database. This is quick and handy if you don’t want to write any TSQL or any other methods. Do let me know if you have any other way to find the same. I look forward to learning from you folks too.

Reference: Pinal Dave (http://blog.sqlauthority.com)

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.

SELECT
CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
ELSE ISNULL(sob.name, '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
WHERE sob.TYPE='U'
GROUP BY sob.name
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 (http://blog.sqlauthority.com)

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

OR

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
MyRecordSet.MoveFirst
WHILE NOT MyRecordSet.EOF
MsgBox(MyRecordSet(“name”).value)
MyRecordSet.MoveNext
WEND
MyRecordSet.Close
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,
cursor_id,
c.session_id,
c.properties,
c.creation_time,
c.is_open,
SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
(
CASE c.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
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
GO

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 (http://blog.sqlauthority.com)

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 DATABASE SQLAuthDB
GO
USE SQLAuthDB
GO
CREATE TABLE EmpMaster(Emp_id INT PRIMARY KEY, FName VARCHAR(10), LName VARCHAR(10))
GO
CREATE TABLE AddressMaster(AddressId INT PRIMARY KEY, Address VARCHAR(100), Status CHAR(1))
GO
CREATE TABLE EmpAddress(Emp_id INT, AddressId INT)
GO

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.

ALTER TABLE dbo.EmpAddress ADD CONSTRAINT
FK_EmpLocation_EmpMaster FOREIGN KEY
(
Emp_id
) REFERENCES dbo.EmpMaster
GO
ALTER TABLE dbo.EmpAddress ADD CONSTRAINT
FK_EmpLocation_AddressMaster FOREIGN KEY
(
AddressId
) REFERENCES dbo.AddressMaster
GO

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')
GO
INSERT INTO AddressMaster VALUES (1, 'Bangalore, India', 'A')
GO

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]
GO

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:

USE MASTER;
DROP DATABASE SQLAuthDB;

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 (http://blog.sqlauthority.com)

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.

Overview

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

Summary

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 (http://blog.sqlauthority.com)

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 (http://blog.sqlauthority.com)

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 (http://blog.sqlauthority.com)