January 14, 2010 at 1:04 pm
Can someone tell me what the heck it's used for or at least point me in the write direction...
EXECUTE sp_MSForEachTable '
select DB_NAME() AS DatabaseName, PARSENAME(''?'',1)as TableName,
--sc.name as IdentityColumn,
ident_current(''?'') as CurrentIdentity,
--count(*) AS Records--,
--ident_current(?) - count(*) AS CurrentLessRecords,
MAX(IDENTITYCOL) AS MaxIdentity--
--max(sc.name) + 1 AS NewReseedValue
from ?
join sys.objects so
on PARSENAME(''?'',1) = so.name
join sys.columns sc
on so.object_id = sc.object_id
where so.type = ''U''
and sc.is_identity = 1
and so.name not like ''MS%''
'
January 14, 2010 at 1:09 pm
It takes the place of the table name in the "for each table" proc. Comparable in the "for each db" proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2010 at 1:30 pm
Thanks. 2 things.
1) i keep getting the following warning when i run it. Do you know why and is it effecting my result st?
Msg 207, Level 16, State 1, Line 4
Invalid column name 'identitycol'.
2) Is there a way to wrap the MSForEachTable w/ the foreachdb so that i can run this for all db's and tables on a sql server instance?
January 14, 2010 at 1:48 pm
Easy trick for dynamic sql question... always do a print before trying to execute and make sure that the printed version does what you want it to do. Save a lot of try / errors.
January 14, 2010 at 2:25 pm
That really doesn't apply in this instance however i agree w/ u on typical dynamic sql querries.
So i mentioned i'm getting the below warning
Invalid column name 'identitycol'.
I think i figured out why the warnings are coming.
I'm trying to get the max identity col value for each table. I'm filtering the inner select to only select from the tables who have an identity column but i think the warning is coming from when the for each table is running this for tables who do not have identities. Therefore it doesnt know what the IDENTITYCOL statement means. I could probably wrap the MAX(IDENTITYCOL) in a case and check to see if the table has a identity and if it does use the IDENTITYCOL else set it to zero or something.
EXECUTE sp_MSForEachTable '
select DB_NAME() AS DatabaseName, PARSENAME(''?'',1)as TableName,
--sc.name as IdentityColumn,
ident_current(''?'') as CurrentIdentity,
--count(*) AS Records--,
--ident_current(?) - count(*) AS CurrentLessRecords,
MAX(IDENTITYCOL) AS MaxIdentity--
--max(sc.name) + 1 AS NewReseedValue
from ?
join sys.objects so
on PARSENAME(''?'',1) = so.name
join sys.columns sc
on so.object_id = sc.object_id
where so.type = ''U''
and sc.is_identity = 1
and so.name not like ''MS%''
'
Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply