ClickCulture Blog - SQL

List all user tables and columns in a database.

Tue, 16 Oct 2007 18:02:00 -0400

List all columns in all tables in a SQL Server 2000 database.

SELECT SO.NAME, SC.NAME
FROM SYSOBJECTS SO INNER JOIN SYSCOLUMNS SC
ON SO.ID = SC.ID
WHERE SO.XTYPE = 'U'
ORDER BY SO.NAME, SC.NAME

List all columns in all tables in a SQL Server 2005 database.

SELECT SO.NAME, SC.NAME
FROM SYS.OBJECTS SO INNER JOIN SYS.COLUMNS SC
ON SO.OBJECT_ID = SC.OBJECT_ID
WHERE SO.TYPE = 'U'
ORDER BY SO.NAME, SC.NAME

Useful MSSQL Scripts

Wed, 10 May 2006 17:39:00 -0400

I use these SQL scripts quite a bit - they work on Microsoft SQL2000.

These are mostly to modify users/owners - usually when moving databases from one box to another. Open up the Query Manager - paste these in (make sure you have the correct database selected and run them.

This will change the owner on all tables back to the dbo:

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
@old = 'username you want to remap'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

Occasionally you will restore a database where the users may not be in sync between SQL Server and the database... Say you restore a database on your local development machine - the database has a user "Joe Smith" but there is no such user on your local machine. If you try to create that user and grant it permission to the database that already has a user by that name - SQL Server will throw an error. Run the following to find these 'orphaned' users - and re-sync them.

To get list of users:

SP_CHANGE_USERS_LOGIN REPORT

To sync users:

SP_CHANGE_USERS_LOGIN 'Update_One', 'joesmith','joesmith'

CSS Organization, ColdFusion Cookbook and Database Models

Fri, 10 Feb 2006 19:06:00 -0400

Short list these week - too busy to surf much!

CSS Organization - some good tips

ColdFusion Cookbook Ray Camdens - super cool CF resource!

A huge list of Database models - nicely laid out by use

Information Architecture Deliverables - good read

Using Access To Cascade Delete A Parent/Child Relationship

Thu, 12 Jan 2006 18:05:00 -0400

Database: Access
Language: ColdFusion/SQL

For a recent project I built a links application which had a parent/child relationship via one table:

CatID > ParentID.

The problem was if you deleted a link category - you would need to also delete any categories under that category (child) as well as any links associated with those categories.

Another negative was there was no way to know the depth of the parent/child relationship so it would take a complex loop to delete everything.

I remember reading something about using the database to do a ‘cascading’ delete so I dug into this and came up with the following solution which lets the database do the heavy work:

First I setup my table as such (notice tblLinkCategory relates to itself - tblLinkCategory_1)

The key to initially setting this relationship is the first record in tblLinkCategory MUST have a NULL value in parentID - since it’s the first record there is nothing to relate to - so a NULL must be used.

Next we just need to setup the cascade - right click on the relationship line between each table to get the properties:

Select “Enforce Referential Integrity” and “Cascade Delete Related Records”

Once you do this you can no longer delete rows with Access open (you will get a ‘table locked’ error) but it works fine via a delete query from ColdFusion.

Now by doing a single delete from the tblLinkCategory table:

delete
from tblLink
where id = <cfqueryparam value="#attributes.id#" cfsqltype="CF_SQL_INTEGER" maxlength="5">

It will delete the category, any sub-categories and any related links to those categories.

Performance Tuning CF, OpenCD 3.1, SQLServer Tips

Wed, 23 Nov 2005 17:17:00 -0400

Bulletproof Web Design Contracts

How sites (our site in particular) look to color blind people

"What I Wish Developers Knew About SQL Server" Great presentation

YACSG (Yet another color scheme generator)

The OpenCD v3.1 is out - great open source software for Windows!

Access to MySQL Converter

Great General Programming Tips

Perfomance Testing ColdFusion