Finding out which tables in a SQL database have no rows

How many times have you looked at a production database and found a table that has no rows in it? Then, when questioning the developers, you are told, “Oh, that table was never used.” (That’s slack undertow.) It happens to me often enough, so I wanted to come up with a way to easily find all the tables in a SQL database that have no rows.

One line of SQL (which actually runs three lines of SQL, once for each table in the database) is all you need:


exec sp_MSforeachtable 'declare @count int; select @count = count(*) from ?; if @count = 0 print ''?'';'

Run that, and the output will list the schema and table name of every table in the current database that has no rows.

Executing native SQL using NHibernate named queries

I’ve been doing a lot of work with NHibernate lately, particularly with named queries. It took a while to get it just right, so I figured it would be helpful to others (and to myself in the future) to note some of the gotchas and how-to steps to get it just right.

What is a named query?

An NHibernate named query is essentially a native SQL statement that can be invoked by NHibernate and can return strongly-typed object data. It allows you to leverage native SQL code — parametrized statements or stored procedures — to perform complex data manipulation and retrieval.

In other words, let’s say you’re writing a baseball game, and you have three objects in your domain model: a Player, a Team, and a DrugTestResult model. You write a stored procedure, spSelectPlayersByLastDrugTestDate, which returns all players who haven’t had a drug test since a given date.

Now that you have a domain model and a SQL statement, how do you execute it through NHibernate?

Mapping your named queries

NHibernate needs to know about your named queries in order to execute them. Much like you tell NHibernate about your domain model using XML mapping files (*.hbm.xml), you tell NHibernate about your named queries using those same files.

Since the named query mappings do not relate directly to your domain model mappings, you can create a separate mappings file just for your named queries. A sample mappings file for our spSelectPlayersByLastDrugTestDate could look like the following.

< ?xml version="1.0" encoding="utf-8"?>
<hibernate -mapping xmlns="urn:nhibernate-mapping-2.2">
<sql -query name="FindPlayerByLastDrugTest">
<query -param name="LastDrugTestDate" />
<return class="Player">
<return -property column="PlayerID" name="PlayerID" />
<return -property column="PlayerName" name="Name" />
<return -property column="TeamID" name="Team" />
<return -property column="LastDrugTestID" name="LastDrugTestResult" />
</return>
exec spSelectPlayersByLastDrugTestDate @LastDrugTest=:LastDrugTestDate
</sql>
</hibernate>

The named query part of the mapping is in the <sql-query> section. I’ll describe its most important sections below.

  • The sql-query tag defines the named query. The name attribute is the name that NHibernate (and, in turn, your code) will use to reference the query. In our example, we are stating that this named query is called FindPlayerByLastDrugTest.
  • The query-param tag defines a parameter in your query. The name attribute reflects the name by which you are referring to that parameter, not the parameter name in the actual SQL statement. The type is the data type of that parameter. In our example, we have one parameter, LastDrugTestDate, which is a DateTime.
  • The return tag defines the type which is returned by the named query. In our example, the return type is a class, Player. Our SQL statement must therefore return fields which map to the Player object. Note that named queries do not have to return domain model objects; they can return ordinals, other types, or nothing at all. (As a result, the return tag is optional.)
  • The many return-property tags tells NHibernate how to map a column in the query results (the column attribute) to a property name in the return type. NHibernate gets rather crafty here. For normal properties (PlayerID and Name in our example) the mappings are simple column-name-to-property-name. Note, however, the TeamID mapping, where we map a TeamID in our result set (which could be an integer, the primary key for a Team domain object) to a property named Team. In our domain model, the Player.Team property is not an integer, it is a Team. NHibernate, because it knows how a Player relates to a Team, will auto-map the player to the team. This nuance is important in getting your named queries to work with your domain model: you map foreign key values in your result set to objects in your data model. (A similar thing happens for the LastDrugTestResult property.)
  • Finally, after we close our return tag, we provide our actual SQL statement. This is a standard parametrized SQL statement with a twist. To inject your query-param values into the SQL statement, you specify :Name — that is, a colon followed by the name specified for the query-param.

Got all that? Good. We’re almost done!

Running your named queries

Finally, the easy part — running the named query! Named queries are run against the NHibernate ISession. Various methods are available to set parameters using a fluent interface.

Running our FindPlayerByLastDrugTest query could look like this:

// get your NHibernate session however you normally would
//ISession session = ...

// create an IQuery based on your named query, specifying parameters
IQuery namedQuery = session.GetNamedQuery("FindPlayerByLastDrugTest")
.SetDateTime(new DateTime(2003, 1, 1));

// execute the query
IList list = query.List();

// if you were running a query that returned a single, scalar value, you could say...
var result = query.UniqueResult();

Note that there are generic versions of List() and UniqueResult(), but I had problems getting them to work. Not sure why (I didn’t dig that far), but the above queries will do the same (though they require some type-casting).

Named queries do a lot more. The following links may prove useful in your learning.

If you find other great resources, be sure to share!

Scripting the deletion of objects in a SQL database (second version)

A few weeks ago, I wrote a post about deleting all tables and constraints in a database. It’s time to take that one step further!

The following SQL code will delete all stored procedures, user-defined functions (UDFs), views, table constraints, and tables (in that order) from a given SQL database. As with the original, you can specify an object name previx by changing the set @tblname = '' as needed.

This is helpful in two situations:

  • When you want to delete all objects in a database without dropping/recreating it, in which case you’d specify '%' as the @tblname.
  • When you want to delete all objects in a database whose name starts with a certain sequence of characters. For example, if you want to delete all the ASP.Net membership objects in a database, you can specify '%aspnet_%' as the @tblname, since its SQL objects start with aspnet_ or vw_aspnet_.

Note that you should be very careful any time you use scripts like this, as data loss can happen where you least expect it. I use this script for clearing out development databases, but would never use it for production databases.

Anyway, on to the script.
Continue reading

Remove anonymous users from ASP.Net Membership tables

If you use the ASP.Net membership tools, have <anonymousIdentification enabled="true" /> specified in your Web.config, and get lots of anonymous visitors, it’s only a matter of time before your database grows. What’s filling it up is the countless user records for your anonymous users.

If you don’t need to track user and profile information for an anonymous user once they leave the site, you can delete the unneeded data by running a SQL script. The following script will delete from your membership tables all anonymous users whose last activity was more than 7 days ago.

delete from aspnet_profile
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

delete from aspnet_usersinroles
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

delete from aspnet_membership
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

delete from aspnet_personalizationperuser
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

delete from aspnet_users
where userid in ( select userid from aspnet_users
where isanonymous = 1
and datediff(dd, lastactivitydate, getdate()) &gt; 7
)

Doing this may be very important to those using a shared hosting plan that has limited SQL disk space, as those anonymous users can quickly eat up disk space. Since each row in aspnet_users takes up just over 1kb, having 1,000 anonymous users will eat up 1MB — something that adds up quick if you only have 250MB of SQL disk storage.

Deleting all tables and constraints in a database

We all need to do it from time to time — erase a bunch of tables in a SQL database. Sure, you can do this through Enterprise Manager, one table at a time, or write a bunch of DROP TABLE statements, but when doing it this way, you also need to ensure all foreign key constraints (i.e. table dependencies) are dropped first.

There’s an easier way — the following script, which was derived from a few posts scattered around the Internet. (Sorry, it’s been a while, and I don’t remember what they were!)

Continue reading