How to truncate all tables except one in MS SQL

It is well-known that a SQL guru can truncate all tables. This is not something anyone is going to do in production. For while coding or testing, this might be a common practice.

To truncate all tables, use the following sql:

EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

However, what if you wanted to exclude one table. For example, if using Entity Framework, one might want to keep the __MigrationHistory table untouched.

EXEC sp_MSForEachTable 'if ("?" NOT IN ''[dbo].[__MigrationHistory]'')
	TRUNCATE TABLE ?'

I finally figured it out by learning how to query the values:

EXEC sp_MSforeachtable 'if ("?" NOT IN ("[dbo].[__MigrationHistory]"))
         SELECT "?"'

It took me a good hour to figure this out. The key was to quote the ? variable.

Back up and restore a single table with foreign keys using SQL Server

Today I needed to backup a single database table, then test a change to the data, then if the change failed, restore the original data.

Below is how I did this.

What I used

I used SQL Server Management Studio to do all of the following steps.
I performed the steps on a database running on a SQL 2012 database server.

Part 1 – Backup the table

SELECT *
INTO MyTable_Bak
FROM MyTable;

Note: This will work usually, however, it won’t work if you have a calculated column. If you have a calculated column, create the table first, then specify the columns you are inserting. I didn’t have a calculated column, so I didn’t take time to figure this out.

Part 2 – Restoring the table

Step 1 – Finding the Foreign Key Constraints

SELECT Name, Object_Name(parent_object_id) as [Table]
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('MyTable')

The results were like this:

Name                    Table
FKDDED6AECAD1D93C0      MyOtherTable1
FK166B6670AD1D93C0      MyOtherTable2

Step 2 – Get the Drop and Create for each Foreign Key

In SQL Management Studio Express, I went to each table in the above list, and did the following:

  1. Locate the foreign key under Database | MyDb | Tables | dbo.MyTable | Keys.
  2. Right-click on the Foreign Key and choose Script Key as | Drop and Create to | Clipboard.
  3. Paste this into the query window.
  4. Delete the USING MyDb statement and separate the DROP statement from the two ALTER TABLE statements.
  5. Repeat for the next foreign key constraint, grouping the DROP statements and the ALTER TABLE statements together.

Step 3 – Run the DROP statements

Run the two DROP statements created above.

ALTER TABLE [dbo].[MyOtherTable1] DROP CONSTRAINT [FKDDED6AECAD1D93C0]
GO
ALTER TABLE [dbo].[MyOtherTable2] DROP CONSTRAINT [FK166B6670AD1D93C0]
GO

Step 4 – Restore the table

I used this query to restore the table from the backup.

SELECT * FROM MyTable
SET IDENTITY_INSERT dbo.MyTable ON; 
TRUNCATE TABLE MyTable ;
INSERT INTO MyTable (Id, Col1, Col2, Col3) -- Specify all columns here
SELECT (Id, Col1, Col2, Col3)              -- Specify all columns again here
FROM MyTable_Bak

Step 5 – Restore the foriegn key constraints

Run the ALTER TABLE scripts you grouped together from Step 2.

ALTER TABLE [dbo].[MyOtherTable2]  WITH CHECK ADD  CONSTRAINT [FKDDED6AECAD1D93C0] FOREIGN KEY([MyTableId])
REFERENCES [dbo].[MyTable] ([Id])
GO

ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FKDDED6AECAD1D93C0]
GO

ALTER TABLE [dbo].[MyOtherTable2]  WITH CHECK ADD  CONSTRAINT [FK166B6670AD1D93C0] FOREIGN KEY([MyTableId])
REFERENCES [dbo].[MyTable] ([Id])
GO

ALTER TABLE [dbo].[MyOtherTable2] CHECK CONSTRAINT [FK166B6670AD1D93C0]
GO

Conclusion

Your table is restored.

Observations

  1. This is a simple process for a table with only a few foriegn key constraints but could be difficult if you have dozens of foreign key constraints.
  2. Also, this process might not work perfectly with calculated columns without changes to the above process.
  3. It should be easier to do this.

If you know of a better way that doesn’t require $oftware, let me know.

SQL Addendum Table

I have an application I am building that needs to be malleable. It is a data-driven application. It will have users, contacts, organizations, and many other objects represented as a database table. One goal of this project is to allow for extension. Some customers are going to want to add a field to an object that our tables don’t include. We want to handle this end to end. It seems the perfect use of a property value table.

It would be pretty easy to create an Addendum table for each object.

dbo.Organization
dbo.OrganizationAddendum
dbo.User
dbo.UserAddendum

While that is OK, it requires additional work every time a table is created. What if a Partner writes a plugin and adds an object in the database? Well, unless the Partner creates an addendum table, this won’t really work.

Is there a way to solve this so any object in the database can have Addendum data?

I came up with this table.

CREATE TABLE [dbo].[LD_Addendum](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Table] [nvarchar](100) NOT NULL,
	[TableId] [int] NOT NULL,
	[Property] [nvarchar](255) NOT NULL,
	[Value] [nvarchar](255) NOT NULL,
	[CreateDate] [datetime2](7) NOT NULL,
	[LastUpdated] [datetime2](7) NULL,
	[CreatedBy] [int] NOT NULL,
	[LastUpdatedBy] [int] NULL,
 CONSTRAINT [PK_Addendum_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])

Then in the Web interface, I could have single template control that works for any object. Whatever object I am editing, be it user, contact, organization or other, the control would exist. If a partner adds a plugin with a new object, the control would exist. Seems easy enough, right?

The problem comes in with some of the features that we would like to be automatically handled on the database side:

  1. Table should have Id column
  2. Table should have four main fields
    • Table <– Database table to add addendum data for
    • TableId <– The row of in the table that addendum is for
    • Property <– The property of the addendum
    • Value <– the value of the addendum data
  3. Table should have the four auditing fields in IAuditTable
    • CreateDate
    • CreatedBy
    • LastUpdated
    • LastUpdatedBy
  4. Only one Property of the same name should exist per table and id. Easily done with a Unique constraint.
  5. Table should have a constraint that enforces that table must exist.
    I found a way to do this: I created User-defined Function (UDF) and check constraint that uses the UDF.

    CREATE FUNCTION [dbo].[TableExists](@TableName NVARCHAR(255))
    RETURNS bit
    AS
    BEGIN
    RETURN ((SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName))
    END
    
  6. Table should have a constraint that enforces that TableId must exist in the Table
    1. Not supported – CLR UDFin C#? Or handle this in code?
  7. The row should delete when Table is deleted. Similar to ON DELETE CASCADE.
    • Not supported – CLR UDFin C#? Or handle this in code?
  8. The row should delete when a row from a table that matches Table and Id is deleted. Similar to ON DELETE CASCADE.
    • Not supported – CLR UDFin C#? Or handle this in code?

Perhaps we ignore the missing features from database side and handle them with code?

Or perhaps another database system other than Microsoft SQL Server (such as Postgresql) could do this?

Scaling

Assuming I got this to work, I see one main problem: Table size. However, I am not sure this is an issue. Tables can quite large, millions of rows. If this table got too big, then we could investigate why, analyze the table, and perhaps move a property value from the Addendum table to an actual column in a real table. This should replace the ability to create a plugin with an additional table, but it should make it so few plugins are needed as there is more extensibility out of the box.

Also, we found that default values often alleviate addendum tables. For example, imagine adding an addendum item to an organization, ContactIntervalInDays. Say a company is supposed to contact their customers every 90 days. However, some customers might require more or less contact. However, the default is 90. Instead of adding 90 to all customers, you set a default. If ContactIntervalInDays is not in the Addendum table, then use 90, otherwise use the value.

Anyway, it seems like an Addendum table is something that most projects and solutions, such as CRMs, Shopping Carts, ERPs, etc. should implement. It won’t solve the most complex issues with extending a product, but it would perhaps solve many of them. The more complex extension can continue to be added via a well-designed plugin architecture.

Unfortunately, this simplistic solution is not supported. The recommendation is to have 1 addendum table for every regular table. Ugh! That doesn’t scale and is not maintainable long term.

Still, I went ahead and requested this feature from the SQL team.

Entity Framework and many WHERE clauses

So today, I needed to get Entity Framework to return me a list of Products from the database based on a list of Product.Name and Product.Version values (not Ids). If it were Product.Id, it would have been simple as I could have used an IN statement, but it wasn’t.

The query might get many (maybe hundreds at a time) products based on the list. Here is the query I imagined.

So when doing a query like this, since there could be hundreds, I have a couple of options.

  1. Query the database once for each product.
    SELECT * FROM dbo.Product
    WHERE (Name = 'Product 1' AND Version = '10.0')
    

    Repeat this same query once for each Product.

  2. Query the database one time with an or clause for each Product.Name and Product.Version.
    SELECT * FROM dbo.Product
    WHERE (Name = 'Product 1' AND Version = '10.0')
       OR (Name = 'Product 2' AND Version = '10.0')
       OR (Name = 'Product 3' AND Version = '10.0')
       OR (Name = 'Product 4' AND Version = '1.0')
       -- There could be hundreds
    
  3. Query the database once and get all products and use code to find the ones I wanted.
    SELECT * FROM dbo.Product
    

Option 1
I didn’t like this option because I could end up doing hundreds of single queries. That doesn’t sound like a good idea. What would the performance impact would be when doing hundreds of single queries? The overhead of traversing over the network to the database would prevent this option from scaling.

Option 2
This is the option I imagined in my head. My gut said to use this option.

Option 3
This would work. We only have about two thousand products today and querying them all would, right now, not be bad at all. However, we just bought a company and will be adding more products. We plan to buy more companies. Also, we have two companies that we have already bought and have yet to add those products in. When would the number of Product rows in the database make the SELECT * and invalid option? Doing this would work now, but it leave a time bomb for some future developer encounter and have to fix.

Winner: Option 2

Problem
Entity Framework doesn’t really have an easy way to create the Option 2 query.

So, how do I create this query with many where statements?

Here are the two options I’ve found:

Inline SQL Query with Entity Framework

        private static List<Product> GetProductsByNameAndVersion(ActivationDbContext dbContext, IEnumerable<ProductRequest> products)
        {
            if (!products.Any())
            {
                return new List<Product>();
            }
            var query = "Select * FROM Product WHERE ";
            var or = "";
            var template = "(Name = '{0}' AND Version = '{1}')";
            foreach (var prod in products)
            {
                query += or;
                query += string.Format(template, prod.Name, prod.Version);
                or = " OR ";
            }
            var dbProducts = dbContext.Products.SqlQuery(query).ToList();
            return dbProducts.ToList();
        }

This option means I have to create magic strings and make sure that I handle the strings correctly. It has bugs already. Such as what if a product only has a name and not a version (version could be null or empty, who knows) or vice-versa? How would this affect my query string?

PredicateBuilder

Predicate Builder from the LinqKit library which is available as a NuGet package.

        private static List<Product> GetProductsByNameAndVersionPredicate(ActivationDbContext dbContext, IEnumerable<ProductRequest> products)
        {
            if (!products.Any())
            {
                return new List<Product>();
            }

            var predicate = PredicateBuilder.False<Product>();

            foreach (var prod in products)
            {
                var inner = PredicateBuilder.True<Product>();
                inner = inner.And(p => p.Name== prod.Name);
                inner = inner.And(p => p.Version == prod.Version);
                predicate = predicate.Or(inner);
            }
            var dbProducts = dbContext.Products.AsExpandable().Where(predicate).ToList();
            return dbProducts;            
        }

PredicateBuilder isn’t very intuitive. For starters, what is the different between these methods:

  • PredicateBuilder.True() – from what I understand this would be more appropriate and understandable as PredicateBuilder.And()
  • PredicateBuilder.False() – from what I understand this would be more appropriate and understandable as PredicateBuilder.Or()

Also, you have to remember to call AsExpandable() on the first call to a table in order to use it.

Conclusion

I am going to go with PredicateBuilder for now. It feels cleaner than rolling my own string query. But both solutions ultimately worked. That means that Entity Framework ultimately provided me a solution without an extra library. However, LinqKit saved me from magic strings. My only question is this: Why isn’t a predicate builder built into Entity Framework?

Add sql constraint on null, empty, or whitespace (C# string.IsNullOrWhiteSpace() equivelant)

Here is a User table. We wanted to make the UserName column not be null, empty, or whitespace.

So the constraint I made is this

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [UserNameNotEmpty] CHECK  (([UserName]<>'' AND rtrim(ltrim(replace(replace(replace([UserName],char((9)),''),char((13)),''),char((10)),'')))<>''))
[UserName]<>''
Empty is checked first and not allowed.
replace([UserName],char((9)),'')
Replaces any instance a Tab character with an empty string.
replace([UserName],char((10)),'')
Replaces any instance a Carriage Return character with an empty string.
replace([UserName],char((13)),'')
Replaces any instance a Line Feed character with an empty string.
ltrim([UserName])
Left trim. It trims spaces from the left side of the string.
rtrim([UserName])
Right trim. It trims spaces from the right side of the string.

Note: You should know that char(9) is tab, char(10) is line feed, char(13) is carriage return.

Here is a complete User table. (This is a legacy system I inherited and I am fixing inadequacies.)

CREATE TABLE [dbo].[User](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](255) NOT NULL,
	[Password] [varchar](255) NOT NULL,
	[Salt] [varchar](255) NOT NULL,
	[FirstName] [varchar](255) NULL,
	[LastName] [varchar](255) NULL,
	[Email] [varchar](255) NULL,
	[Active] [bit] NOT NULL
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Active]  DEFAULT ((1)) FOR [Active]
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [UserNameNotEmpty] CHECK  (([UserName]<>'' AND rtrim(ltrim(replace(replace(replace([UserName],char((9)),''),char((13)),''),char((10)),'')))<>''))
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [UserNameNotEmpty]

Notice, I don’t check null on the constraint, as NOT NULL is part of the UserName column design.