Re: query for identity columns
From: Gregory A. Larsen (greg.larsen_at_netzero.com)
Date: 05/20/04
- Next message: Bret: "Creating temp table"
- Previous message: Tom Edelbrok: "Can error messages in Query Analyzer be suppressed?"
- In reply to: Hassan: "Re: query for identity columns"
- Next in thread: Gregory A. Larsen: "Re: query for identity columns"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 20 May 2004 14:09:35 -0700
Try this:
master.dbo.sp_MSforeachdb 'SELECT ''?'' , TABLE_NAME, COLUMN_NAME,
DATA_TYPE, IDENT_CURRENT(TABLE_NAME)
FROM ?.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ''dbo''
AND COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),
COLUMN_NAME, ''IsIdentity'') = 1
ORDER BY TABLE_NAME'
-- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---- Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples "Hassan" <fatima_ja@hotmail.com> wrote in message news:O8QA0qqPEHA.1620@TK2MSFTNGP12.phx.gbl... > Neat Anith.. > > How can i take it to the next step and run the same for every database. I > know theres something tricky while using a cursor for each database > > I would like to run this query on a particular server and list all the > columns you provided except for that it loops through all user databases > too. Could you help me here > > Thanks > > "Anith Sen" <anith@bizdatasolutions.com> wrote in message > news:eeGUiUqPEHA.644@tk2msftngp13.phx.gbl... > > Do: > > > > SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENT_CURRENT(TABLE_NAME) > > FROM INFORMATION_SCHEMA.COLUMNS > > WHERE TABLE_SCHEMA = 'dbo' > > AND COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), > > COLUMN_NAME, 'IsIdentity') = 1 > > ORDER BY TABLE_NAME ; > > > > -- > > Anith > > > > > >
- Next message: Bret: "Creating temp table"
- Previous message: Tom Edelbrok: "Can error messages in Query Analyzer be suppressed?"
- In reply to: Hassan: "Re: query for identity columns"
- Next in thread: Gregory A. Larsen: "Re: query for identity columns"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
