Tuesday, July 24, 2007

Foreign keys for the referenced table

Let's say, I have a database table "SingleTable" and I want to get list of all tables which are referencing to this SingleTable and names of foreign keys...

(I need this, for example, if I am going to drop SingleTable but I need drop all dependencies from this table first )

I have option to use undocumented stored procedure sp_MStablerefs, however I am trying to avoid using undocumented feature. Never know when and where Microsoft will put stick in my wheels :)
So, there is alternative way I found by experiments:

USE mydatabase
SELECT name as fkname, OBJECT_NAME(parent_obj) as fkTableName
FROM sysobjects
INNER JOIN sysreferences
ON sysobjects.id=sysreferences.constid
WHERE name like 'FK%' AND xtype='F' -- foreign key only
AND rkeyid=object_id(N'SingleTable')

Result is set of forein key names and table names referencing to my table..
Bingo. Now we can drop them one by one using cursor, or DataSet on the upper code level..

Wednesday, July 11, 2007

IIS6: separated application pool

When IIS runs multiple versions of .NET web projects, collision happens sometimes.
So, I required to assign my web project in separated application pool under Windows 2003 /IIS6 programmatically.

It is easy through System.DirectoryServices of .NET
There is, I create virtual directory "myWebDir" and assign it to new pool with the name "myAppPool":

DirectoryEntry folderRoot = new DirectoryEntry("IIS://localhost/W3SVC/1/Root");
DirectoryEntry virDir = folderRoot.Children.Add("myWebDir", "IIsWebVirtualDir");
object[] param ={ 0, "myAppPool", true };
virtDir.Invoke("AppCreate3", param);
//set virtual directory properties

Application pool is created automatically if third parameter is a true in the param array.

However, I would like also to manage my application pools without specific virtual directory.
Here are examples to create/remove application pools:

Create Application pool:

string appPoolName = "myAppPool";
DirectoryEntry poolRoot = new DirectoryEntry("IIS://localhost/W3SVC/AppPools");
if (!DirectoryEntry.Exists(IISAppPoolRootPath + "/" + appPoolName);
DirectoryEntry pool = poolRoot.Children.Add(appPoolName, "IIsApplicationPool");

Remove Application pool:

string appPoolName = "myAppPool";
DirectoryEntry poolRoot = new DirectoryEntry("IIS://localhost/W3SVC/AppPools");
DirectoryEntry pool = poolRoot.Children.Find(appPoolName, "IIsApplicationPool");
if (pool != null)

Remove only works, if no one web application assigned to pool.