primary key help (immediate) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

primary key help (immediate)

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-06-13 : 09:38:25

all identity and not null column set primary key.how can i write t-sql command for all identity and not null column ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-13 : 09:44:24

This?


CREATE TABLE foo
    [FooID] INT IDENTITY(1,1) NOT NULL
    , [FooVal] VARBINARY(255)
    , CONSTRAINT PK_Foo PRIMARY KEY CLUSTERED ([FooID])
    )

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-06-13 : 09:48:42

no;
I think i must find all integer and notnull and identity column after
write alter command fot set a primary key maybe i can use cursor.

quote:
Originally posted by Transact Charlie

This?

CREATE TABLE foo
[FooID] INT IDENTITY(1,1) NOT NULL
, [FooVal] VARBINARY(255)
, CONSTRAINT PK_Foo PRIMARY KEY CLUSTERED ([FooID])
)

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-13 : 09:55:31

Are you looking for all columns in your database that are identity columns?

then try this:


SELECT
	st.[name] AS [TableName]
	, sc.[name] AS [ColumnName]
	, sty.[name] AS [Type]
FROM
	sys.tables AS st
	JOIN sys.columns AS sc ON sc.[object_id] = st.[object_id]
	JOIN sys.types AS sty ON sty.[system_type_id] = sc.[system_type_id]
WHERE
	sty.[name] = 'int'
	AND sc.[is_nullable] = 0
	AND sc.[is_identity] = 1

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-06-13 : 10:00:32

this is okay but how can generate alter add primarykey script for this query result?

quote:
Originally posted by Transact Charlie

Are you looking for all columns in your database that are identity columns?

then try this:

SELECT
st.[name] AS [TableName]
, sc.[name] AS [ColumnName]
, sty.[name] AS [Type]
FROM
sys.tables AS st
JOIN sys.columns AS sc ON sc.[object_id] = st.[object_id]
JOIN sys.types AS sty ON sty.[system_type_id] = sc.[system_type_id]
WHERE
sty.[name] = 'int'
AND sc.[is_nullable] = 0
AND sc.[is_identity] = 1

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-13 : 10:10:47

Borrowing from Charlie, I think you want this?
SET NOCOUNT ON
SELECT 'ALTER TABLE ' + st.[name] +char(10)+'ADD CONSTRAINT PK_'+st.name+'_'+sc.name +' PRIMARY KEY('+sc.name+')'
--, sc.[name] AS [ColumnName]
--, sty.[name] AS [Type]
FROM
sys.tables AS st
JOIN sys.columns AS sc ON sc.[object_id] = st.[object_id]
JOIN sys.types AS sty ON sty.[system_type_id] = sc.[system_type_id]
WHERE
sty.[name] = 'int'
AND sc.[is_nullable] = 0
AND sc.[is_identity] = 1

Jim

Everyday I learn something that somebody else already knew

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-06-13 : 10:25:55

sorry -- real work got in the way rather than spoon feeding....


SELECT
	st.[name] AS [TableName]
	, sc.[name] AS [ColumnName]
	, sty.[name] AS [Type]
	, 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(st.[schema_id])) + '.' + QUOTENAME(st.[name]) + ' ADD CONSTRAINT ' + QUOTENAME('PK_' + st.[name] + '_' + sc.[name]) +' PRIMARY KEY(' + QUOTENAME(sc.[name]) + ')'
FROM
	sys.tables AS st
	JOIN sys.columns AS sc ON sc.[object_id] = st.[object_id]
	JOIN sys.types AS sty ON sty.[system_type_id] = sc.[system_type_id]
WHERE
	sty.[name] = 'int'
	AND sc.[is_nullable] = 0
	AND sc.[is_identity] = 1
	
	AND NOT EXISTS (
		SELECT 1
		FROM sys.key_constraints
		WHERE [type_desc] = 'PRIMARY_KEY_CONSTRAINT'
		  AND [parent_object_id] = st.[object_id]
		  )

NB: I see others got there first! Thanks for helping out!
Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources