|
|
|
SSChampion
Group: Administrators
Last Login: Today @ 4:43 PM
Points: 21,861,
Visits: 6,081
|
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885,
Visits: 1
|
|
Hi there
I use this before running my data migration scripts for the bazillionth time...
SELECT 'DBCC CHECKIDENT (' + o.[name] + ', RESEED, 1)'
FROM syscolumns c
INNER JOIN sysobjects o ON o.[Id] = c.[Id]
INNER JOIN sysusers u ON o.[uid] = u.[uid]
WHERE c.[status] & 128 = 128
AND u.[name] = 'dbo'
AND o.[name] <> 'dtproperties'
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Today @ 4:43 PM
Points: 21,861,
Visits: 6,081
|
|
Nice script. Did you post it? I usually am fixing one-off things, but this would be handy for migrating data.
Steve Jones
steve@dkranch.net
|
|
|
|
|
SSCertifiable
Group: Moderators
Last Login: 2 days ago @ 6:28 AM
Points: 6,361,
Visits: 900
|
|
DMO has two methods, CheckIdentityValue and CheckIdentityValues that deal with this. The first works on a table, the second checks ALL tables in a db - equivalent to the script Chris has - the caveat being that it doesnt accept any parameters so you get the default behavior of DBCC CheckIdent which is to only fix it if there is a problem. Think I'll drop a note to MS to see if they can fix this!
Andy
Andy
SQLShare - Learn One New Thing Each Day
It Depends - My Professional Blog
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885,
Visits: 1
|
|
Hi Steve
Yep, just submitted it. Funny enough, a lot of SS developers seem to think that identities for EVERY table is a great solution because it means less coding and simplification when traversing joins. Coming from a 3nf+ world, at times I find it hard to justify it, especially when the natural key remains in place and busines logic is still required to maintain it anyhow. Just something ive noticed over the years and still battle with today.
Cheers
Ck.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, March 03, 2009 2:48 PM
Points: 1,
Visits: 3
|
|
If the Identity column is reset what happens to all relationships do they loss there related records?
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Today @ 4:43 PM
Points: 21,861,
Visits: 6,081
|
|
An identity column has no "relationships" by default. If you want an identity to be the PK or an FK, you set that separately. If you reset the identity value, you could cause a pk violation if it is the pk.
Steve Jones
steve@dkranch.net
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Saturday, April 28, 2007 5:47 PM
Points: 18,
Visits: 1
|
|
the problem is we need db_owner or sysadmin to execute 'DBCC CHECKIDENT '.
what about if user is a public role?
my problem is i have a store porcedure with 'DBCC CHECKIDENT '.
But a public role user cann't run it.
|
|
|
|
|
SSChampion
Group: Administrators
Last Login: Today @ 4:43 PM
Points: 21,861,
Visits: 6,081
|
|
There are some utilities, like checkident and set identity_insert, that are not designed to allow anyone to use them. The reason is likely that often the developer's have not thought threw the impact of having people run them
Why do you need public to run this. I'd argue this is a bad idea. It should be controlled and run by someone with admin type privldges.
On a side note, granting rights to public is a bad idea.
Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Thursday, July 05, 2007 6:14 AM
Points: 10,
Visits: 1
|
|
I just finished forward engineering my database. This works like a charm when deleting and inserting records for testing. I am creating a business process that does allow for manual and automatic record inserts updates and deletes. The automatic data manipulation occurs in SSIS. The manual data manipulation comes from the user via .NET application. It appears that this would work really well for both processes in order to keep the identities in squential order. Is there a significant downside in using it in a .NET application where users have the ability to delete and insert records ad infinitum? Sorry for being Vague; however, there currently aren’t a specified number of times that the user has before they no longer have access to the table. Thanks!
|
|
|
|
