Category: SQL How-To

Working with SQL Server Data Tools I’ve faced with a demand to extract data-tier application many times. One of the appropriate solution is to use PowerShell script to extract the database to .dacpac file. Thanks to Gianluca Sartori, he gave a good script to extract DACPAC from all databases (see here).

Based on his script, I’ve did some modification to make it work with SQL Server 2014. I’ve found that I have 3 versions of sqlpackage.exe (110, 120 and 130). So the script looks for the latest one and run it.

Here is the final script:

To execute this script, run SQL Server PowerShell (sqlps) and type one of the following commands (they are identical, but use slight different syntax):

• extract_dacpac.ps1 "(local)" Clients
• extract_dacpac.ps1 -server "(local)" -database Clients

If everything is OK, you’ll see the progress of execution.

PowerShell script can downloaded as zip file from this link extract_dacpac

Now I’m investigating Entity Framework and how it creates tables and other database objects. It’s surprising that if you have a reference (foreign key) in your model, EF creates an index in the referenced table during the migration process by default. So, if I have just 5 distinct referencing values with a roughly equal quantity of referenced records (in this case about 20% for each value), SQL Server will never use that index.
To put the creation of database objects under control, I need a simple script to see what objects are really created after migration. There are 3 queries in the script that list the following information:

• columns, their data types and null/not null information
• primary/foreign keys, and referencing table and columns
• clustered/non-clustered indexes and columns

Occasionally I’ve faced with the task to change the value of the primary key, say, in Employee table in SQL Server database. Everything would be OK if this table is not referenced from the other tables. So when I change the value in primary key EmployeeId column, I would break the foreign key constraints.

When you create a table you can specify a REFERENCES … ON UPDATE CASCADE clause, so all changes in primary key would be propagated to the underlying tables (look here for CREATE TABLE syntax and here for referential integrity constraints). But this is not my case, I have the default NO ACTION option. I want to know all tables and columns that are referencing my primary key. This can be done by one of the three ways:

1. stored procedure
   
   Transact-SQL

   EXEC sp_fkeys N'Employee'

   1	EXEC sp_fkeys N'Employee'

   
   UPDATE 2016-02-01:
   If a table is not in a dbo schema, add a second parameter for owner.
2. using system views from sys schema
   
   Transact-SQL

   SELECT OBJECT_NAME(constraint_object_id) AS [FK Name], OBJECT_NAME(referenced_object_id) AS [PK Table], COL_NAME(referenced_object_id, referenced_column_id) AS [PK Column], OBJECT_NAME(parent_object_id) AS [FK Table], COL_NAME(parent_object_id, parent_column_id) AS [FK Column] FROM sys.foreign_key_columns WHERE referenced_object_id = OBJECT_ID(N'Employee', N'U');

   1 2 3 4 5 6 7

   SELECT OBJECT_NAME(constraint_object_id) AS [FK Name],   OBJECT_NAME(referenced_object_id) AS [PK Table],   COL_NAME(referenced_object_id, referenced_column_id) AS [PK Column],   OBJECT_NAME(parent_object_id) AS [FK Table],   COL_NAME(parent_object_id, parent_column_id) AS [FK Column] FROM sys.foreign_key_columns WHERE referenced_object_id = OBJECT_ID(N'Employee', N'U');

3. using INFORMATION_SCHEMA views
   
   Transact-SQL

   SELECT c.CONSTRAINT_NAME, pk.TABLE_NAME AS [PK Table], pkcol.COLUMN_NAME AS [PK Column], fk.TABLE_NAME AS [FK Table], fkcol.COLUMN_NAME AS [FK Column] FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON c.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol ON pk.CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol ON fk.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME WHERE pk.TABLE_NAME = N'Employee';

   1 2 3 4 5 6 7 8 9 10 11

   SELECT c.CONSTRAINT_NAME, pk.TABLE_NAME AS [PK Table], pkcol.COLUMN_NAME AS [PK Column], fk.TABLE_NAME AS [FK Table], fkcol.COLUMN_NAME AS [FK Column] FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON c.UNIQUE_CONSTRAINT_NAME = pk.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcol ON pk.CONSTRAINT_NAME = pkcol.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE fkcol ON fk.CONSTRAINT_NAME = fkcol.CONSTRAINT_NAME WHERE pk.TABLE_NAME = N'Employee';

These methods work perfectly when you’ve set foreign keys. There may be a situation when you have a logical reference, but it’s not implemented as a constraint. You can try to search for a column name. Maybe you will be lucky if you have strong naming conventions and you include PK column name in FK column name, i.e. primary key is EmployeeId, so the foreign key will be BossEmployeeId or ManagerEmployeeId. If you don’t follow this rule, you might have simple name like ManagerId and it will be more harder to guess where the reference might be.

This query could help you to find tables by column name.

UPDATE 2016-04-13:
Modified version of the above query that include schema name.

SQL Server 2008 had introduced one interesting feature that helps to create “inline” tables. It’s a table value constructor. It is commonly used when you need to insert few values or few rows of values into the table. Something like this:

But there’s another case where you can use this kind of tables. For example, you need to update few records, you know their IDs and values to be put to table. Here you can write your update like the following code:

In this update command I join table Employee from database with “inline” table T that was constructed in-flight from 4 pairs of values. It’s much easier to write one command, especially if it’s rather complicated, than to repeat it again and again.

Update 2015-12-10
You can even create a temporary table with table value constructor like this.

You can get the following characteristics about your SQL Server:

• product version (for example, 12.x for SQL Server 2014)
• product level (RTM, Service Pack or CTP)
• edition (standard, enterprise, developer, etc.)

Product Version Product Level Edition
-------------------- -------------------- ------------------------------
12.0.2495.0 RTM Developer Edition (64-bit)

(1 row(s) affected)

The full list of server properties can be seen in MSDN article SERVERPROPERTY (Transact-SQL)

For the database you can get the current internal version number and compatibility level. With some extra effort you can get starting database version number, i.e. the version at which the database was created originally.

Internal Version Number
------------------------------
782

(1 row(s) affected)

To see the starting internal database number you should use DBCC DBINFO command.

DBINFO STRUCTURE:

dbi_version = 782 dbi_createVersion = 661 dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 0:0:0 (0x00000000:00000000:0000)
...

Look at the first line starting with dbi_. The dbi_version is the current internal database version number (782 is SQL Server 2014), the dbi_createVersion is that of the moment of database creation (661 is 2008 R2). The list of these numbers can be found at this post.

If you are using SQL Server 2008 or higher you can query the sys.partitions catalog view. The filed [rows] shows the number of records in each partition of the database table.

This query returns the schema and table names, and total number of rows. If you have only one partition for each table, you can wipe out SUM() and GROUP BY from the query.

Recently I had to examine the content of almost all tables in all databases to find necessary tables and recreate the entity model. So I need a script that can iterate thru all tables in all databases on a given server.

This can be made with the help of undocumented stored procedure sp_msforeachdb. The examples of use can be found via Google search.Here is my script that lists all tables on a MS SQL server.

Recently I’ve faced with a task to extract client names and phone numbers from XML document and return a table like a standard SELECT query. This can be made with a nodes() method.

This method shreds the XML document and extracts nodes from it. The description is available on MSDN. Let’s look at the example how it works.

When I need to analyze dependencies, I should find the answer for 2 questions:

1) which objects does my piece of code depends on? In this case, I get the impact from the others.
When you look at this view definition, you understand that the view depends on two tables.

2) which objects does my piece of code influence on? Now I produce the impact to others.

Microsoft SQL Server has some capabilities to analyze dependencies.

• catalog view sys.objects

• INFORMATION_SCHEMA views

This is a quite simple task to do with cursor because we need to execute command DROP PROCEDURE. So we create a cursor, fetch all names of stored procedure and drop them.

Here’s the script that implement the above logic.

Update 2015-11-26: The original script with SELECT name FROM sys.objects … works well if you have only the dbo schema. To delete all stored procedures in all schemas we need to use two-part objects names schema.object.