Search This Blog

Friday, January 20, 2012

Get identity column seed values

Writing a routine to fetch identity column seed values for tables in a SQL 2008 database
Looking to import data into a database and I need to allow room when I do insert with identity_insert on

                using (SqlConnection conn = new SqlConnection(mMRISConnConfig.ConnectionString))
                {
                    string mrisSQL = @"SELECT IDENT_SEED(TABLE_NAME) AS Seed,
                               IDENT_INCR(TABLE_NAME) AS Increment,
                               IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
                               TABLE_NAME
                               FROM INFORMATION_SCHEMA.TABLES
                               WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
                               AND TABLE_TYPE = 'BASE TABLE'
                               AND TABLE_NAME in ('W_PATIENT','W_REFERRING_PHYSICIAN','W_REFERRING_PHYSICIAN_LOCATION')";

                    conn.Open();
                    SqlCommand cmd = new SqlCommand(mrisSQL, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
}

No comments: