New answers tagged

0

-- disable all constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- delete data in *ALL* tables (not just refTables) EXEC sp_MSForEachTable "DELETE FROM ?" -- reseed the indexes so primary keys start over at 1 declare @cmd varchar(4000) declare cmds cursor for select 'DBCC CHECKIDENT(''' + t.name + ''', RESEED, 0);' FROM sys.tables ...


4

The behaviors you're curious about are explained in the documentation: When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true: ...- The identity column is part of an expression. ...If any one of these conditions is true, the column is created NOT ...


3

Using UPDATE, DELETE or INSERT to move data can take quite a lot of time and use resources (IO) on both data and log files/disks. It is possible to avoid filling the transaction log with potentially lots of records while working on a big table: Using partition switching, only the metadata is changed. There is no data movement involved and this is therefore ...


6

The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows. First off, some test data to play with: CREATE TABLE ...


3

If you want to start with a new identity value you need to reseed your identity. Have a look at the documentation for CHECKIDENT DBCC CHECKIDENT (yourtable, reseed, starting point)


1

Community Wiki answer for content originally provided in the question by DnL SELECT -- in case that reseed will not work and u need a specific number u can use this script -- just delete the ' before DBCC --'DECLARE @identity INT SELECT @identity= '+clmn.name+' --FROM '+SCHEMA_NAME(schema_id)+'.'+tbl.name+' PRINT @identity 'DBCC ...


2

Using the script from the Question as a starting point, you might be better off using the following query: SELECT N'DBCC CHECKIDENT (''' + SCHEMA_NAME(tbl.[schema_id]) + N'.' + tbl.name + N''', RESEED); -- WITH NO_INFOMSGS;' , clmn.seed_value , clmn.increment_value , clmn.last_value FROM sys.tables tbl INNER JOIN ...



Top 50 recent answers are included