Extracting DACPAC From SQL Server Database With PowerShell

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.
Extract DACPAC from database

PowerShell script can downloaded as zip file from this link extract_dacpac

Listing Columns, Keys and Indexes For Specific Table

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

Finding References (Foreign Keys) To This Table

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

    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
  3. using INFORMATION_SCHEMA views

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.

Table Value Constructor in T-SQL aka SELECT FROM VALUES

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.

View Instance and Database Versions

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

(1 row(s) affected)


(1 row(s) affected)

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


DBINFO @0x000000000E29D8E0

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.

List Row Count on All Tables in SQL Server Database

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.

List All Tables in All Databases in SQL Server

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.

Converting XML Nodes to Table in 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.

The nodes() method extracts Client node from XML and makes 2 rows from which we can get values.
Simple XML shred
We can also extract phone numbers from Phone node. In this case we should use common table expression (CTE) to extract Client nodes, then we can shred the Phone nodes.

The result is shown below.
Nested XML shred

Finding Dependencies Between Database Objects in SQL Server

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
This view shows all database objects and dependencies between parent and child objects such as constraints and triggers. Additional info can be found in MSDN
There are 20 views that can obtain various information about database metadata from different angles. Full list is in MSDN
  • system stored procedure sp_depends

According to information in MSDN, this stored procedure will be removed in the future.

  • dynamic managements functions sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities


Deleting All Stored Procedures In SQL Server Database

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.

If we change the object type in first WHERE clause (for example, ‘FN’ for scalar functions) and use appropriate command in EXEC statement (DROP FUNCTION in this case) we can delete all database objects of another type. Full list of object types can be found here