Avoiding Unicode issues when inserting XML into a SQL Database

If you have to insert XML into a SQL Server (2005+) XML column, there's three ways of doing it...

Click to read more ...

SQL create table reference

Here's a quick reference for scripting table creation in SQL server by hand, where the text is readable and pretty rather than an autogenerated mess.

CREATE TABLE sometable
(
    [id] [uniqueidentifier] NOT NULL
        CONSTRAINT [DF_sometable_id] DEFAULT (newid()), /* This isn't required for DEFAULT */
    [userid] [uniqueidentifier] NOT NULL,
    [accountid] [uniqueidentifier] NOT NULL,
    [activated] [bit] NOT NULL,
    [activatedon] [datetime] NULL,
    [email] [nvarchar](128)  NOT NULL,
    [key] [varchar](32)  NOT NULL,

    PRIMARY KEY NONCLUSTERED (id), /* DONT USE CLUSTERED FOR uniqueidentifier */
    CONSTRAINT FK_sometable_userid FOREIGN KEY(userid) REFERENCES users (id),
    CONSTRAINT FK_sometable_accountid FOREIGN KEY(accountid) REFERENCES accounts (id),
)

CREATE NONCLUSTERED INDEX [idx_anothertable] ON [dbo].[anothertable] 
(
    [sometypeid] ASC,
    [fileid] ASC,
    [layoutid] ASC
)

It's usually easier to bulk add the foreign keys after, that way the order of the CREATE TABLES doesn't matter:

ALTER TABLE [dbo].[sometable] ADD CONSTRAINT [FK_sometable_users_id] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([id])

Printing all tables in a database with Management.Smo

The Microsoft.SqlServer.Management.Smo namespace and assembly is used by Management Studio for its interogation of the database. If you have Management Studio installed you can use this in your own applications, a simple example of which is shown below.

// From microsoft.sqlserver.smo.dll
using Microsoft.SqlServer.Management.Smo;

void PrintTables()
{
    Server server = new Server("(local)");
    server.ConnectionContext.ConnectAsUserName = "sa";
    server.ConnectionContext.ConnectAsUserPassword = "Passw0rd";

    Database db = server.Databases["mydatabase"];
    foreach (Table table in db.Tables)
    {
        Console.WriteLine(string.Format("[{0}]",table.Name));

        foreach (Column column in table.Columns)
        {
            Console.WriteLine(string.Format("\t{0} {1} {2} {3}",
                column.Name,
                column.DataType.Name,
                column.Default,
                column.DataType.MaximumLength));
        }

        Console.WriteLine("");
    }

    Console.Read();
}

Binary upload to SQL Server in C#

• Download

This tool from 2004 is useful if you need to do a quick update of a binary column in a SQL server 2000 table. It could be easily adapter for other dbs such as Access or mySQL.

It also gives a good example enumerating all databases, tables and columns from a SQL Server database.

As with all the old C# code on the site, the tool is very basic and not intended to be any shiney finished application.