Recently in SQL Category

Previously: Sql Server 2005 - Surface Area Configuration won't save

Just a quick update; installing Service Pack 3 (yes, I know I said SP2 in the previous post, my bad!) was a non-starter as the SP install attempted to connect to the instance and failed. The message did point me in the direction of the Sql Native Client being corrupted.

Uninstalling and reinstalling the Native Client allowed the service pack to install, allowed the local Sql Server Management Studio to connect and also allowed me to change the "allow external connections" from "Local Only" to "Local & Remote Connections" on both named pipes and TCP/IP. Unfortunately, I can't prove that this allowed external connections to succeed as I didn't have access to another machine on the network.

This morning, the machine is once again refusing to save the "external connections" settings in the Surface Area Configuration Manager, again! :( (This may have something to do with anothe person working on SSL on IIS on the box concerned though, perhaps?)

After a schema change in a SQL Server database, if you have any views that contain a SELECT * statement in them referring to a changed table, you're almost certainly going to get gibberish (at best!) out of those views.

They're fantastically useful things which make it easy to access metadata in SQL Server in a (theoretically!) version agnostic manner. I've mentioned them (or similar) in SQL Server 2005: sysXXXX tables and Finding the Foreign Keys for a table.

Something that's long been on my list of things to do is to explore the INFORMATION_SCHEMA views and re-code some things which I use that directly reference the sysXXXX tables so that they use INFORMATION_SCHEMA views. One of these is a one line script which generates a resultset containing one row for each Foreign Key in a database, eg. "DROP CONSTRAINT [xxxxxxxxxxx]". Very useful, but if all is to be believed, it won't work in SQL Server 2005.

Related Links:

• [DevX.com] INFORMATION_SCHEMA: A Map to Your Database
  A quick, and quite useful overview of INFORMATION_SCHEMA views. Explains both the pros and cons.


• [msdn] SQL Essentials: Foreign Key Metadata: Improving on INFORMATION_SCHEMA views
  An interesting article on extending the INFORMATION_SCHEMA views to increase their usefulness.

According to SQL Server 2005 Tables and Views, as of MSSQL 2005, direct access to system tables like sysobjects and syscolumns won't be (easily) possible. Unless the replacement views that Microsoft are providing are:
1 - Just as useful
or
2 - Usable for directly updating sysXXXX tables
or
3 - Supplemented by a more robust replication system that doesn't require farting about with sys tables once a week to sort it out!

Bah

For a while it's been driving me barmy, trying to obtain a list of Foreign Keys which reference a particular table. Below is the end result of my hunting. No doubt the code could be optimised, which is something I should do. As it uses the INFORMATION_SCHEMA views, it is at least SQL Server version independent and thus should work on both 2000 and 2005. (Not tested on SQL Server 7)

I'm sure there's probably a way to do this in SQLDMO, which will be my next investigation. Watch this space!

IF EXISTS (SELECT name FROM sysobjects WHERE name = N'GetFKsForTable' AND type = 'P') DROP PROCEDURE GetFKsForTable

GO

CREATE PROCEDURE GetFKsForTable
@dbname sysname,
@sTable varchar(255)
AS

DECLARE @sCurrentDB varchar(255);

SELECT @sCurrentDB = SD.name
FROM master..sysprocesses SP
INNER JOIN master..sysdatabases SD
ON SP.dbid = SD.dbid
WHERE SP.spid = @@SPID

IF LOWER(@sCurrentDB) <> LOWER(@dbName)
BEGIN
RAISERROR
('TARGET database is not CURRENT database. Aborted', 18, 1) WITH NOWAIT
RETURN
END

SELECT
CCU.TABLE_NAME AS 'FK_ORIGIN_TABLE',
CCU.COLUMN_NAME AS 'FK_ORIGIN_TABLE_COL',
CCU2.TABLE_NAME AS 'FK_OWNING_TABLE',
RC.CONSTRAINT_NAME AS 'FK_NAME',
CCU2.COLUMN_NAME AS 'FK_COLUMN'
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON CCU.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2
ON CCU2.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE
CCU.TABLE_NAME = @sTable
GO