Never been to CodeSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

About this user

Christopher Dale

4 total

On This Page:

  1. 1 Fix orphaned users in MSSQL
  2. 2 Reset identity seed for a table
  3. 3 Scripting schema updates in MSSQL #1
  4. 4 Retrieve MSSQL table information

Fix orphaned users in MSSQL

to users sql mssql microsoft data transact orphan orphaned user restore restoration restoring by chrelad on Dec 11, 2007
Thanks to CodeProject for this code

Run this in the Query Analyzer (make sure the right database is selected first).

sp_change_users_login 'auto_fix', 'UserName'

Reset identity seed for a table

to sql reset microsoft column transact reseed seed identity ident field ms dbcc checkident by chrelad on Nov 16, 2007
This SQL query will reset an identity seed to the specified value.

Thanks to this site for the answer:

http://www.thescripts.com/forum/thread657059.html

I know this works for MSSQL 2000 at least :)

DBCC CHECKIDENT('mytable', RESEED, 0) ;

Scripting schema updates in MSSQL #1

to sql tsql database mssql schema update tables microsoft columns query scripting information_schema alter altering by chrelad on Aug 08, 2007 1 Comments
This SQL statement will alter a table only if a column in the table to be altered does NOT contain a column named 'FavoriteColorId'.

From: http://haacked.com/

IF NOT EXISTS
(
    SELECT * FROM [information_schema].[columns]
    WHERE    table_name   = 'Customer'
    AND      table_schema = 'dbo'
    AND      column_name  = 'FavoriteColorId'
)
BEGIN
    ALTER TABLE [dbo].[Customer]
    ADD FavoriteColorId int

Retrieve MSSQL table information

to sql server tsql mssql table microsoft column information properties sysproperties systables data type types length columns by chrelad on Aug 08, 2007
Retrieve tons of information (length, data type, name, etc.) of columns for a table. Works in (T|MS)SQL (all I've tested it in).

SELECT * FROM information_schema.columns WHERE table_name = 'Customers'
4 total

On This Page:

  1. 1 Fix orphaned users in MSSQL
  2. 2 Reset identity seed for a table
  3. 3 Scripting schema updates in MSSQL #1
  4. 4 Retrieve MSSQL table information