sql给已有表添加主键

最新推荐文章于 2024-08-22 17:06:19 发布

zzx3q

最新推荐文章于 2024-08-22 17:06:19 发布

阅读量8.5k

点赞数 2

分类专栏： MS SQL 文章标签： sql primary key

本文链接：https://blog.csdn.net/zzx3q/article/details/42583409

版权

MS SQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

/*
ALTER TABLE [dbo].[cg_YearAssessZp] ALTER COLUMN YearAssessZpGUID UNIQUEIDENTIFIER  NOT NULL;

ALTER TABLE [dbo].[cg_YADetailHistory] 
ADD CONSTRAINT [PKC_cg_YADetailHistory] PRIMARY KEY CLUSTERED  ([YearAssessZpGUID]) ON [PRIMARY];
*/
IF EXISTS(SELECT TOP 1 1 FROM sysobjects WHERE id=OBJECT_ID('proc_AddPrimaryKey') AND type='P')
DROP PROCEDURE proc_AddPrimaryKey;
GO

CREATE PROCEDURE proc_AddPrimaryKey
    (
      @tablename VARCHAR(40) ,
      @pkname VARCHAR(40)
    )
AS 
    BEGIN
        IF NOT EXISTS ( SELECT TOP 1
                                1
                        FROM    sys.columns
                        WHERE   object_id = OBJECT_ID(@tablename)
                                AND name = @pkname ) 
            BEGIN
                PRINT 'Not Exists PKName'
                RETURN
            END
        
        DECLARE @coltype VARCHAR(40);
        SELECT  @coltype = b.name
        FROM    syscolumns a ,
                systypes b
        WHERE   a.id = OBJECT_ID('test_test')
                AND a.name = 'id'
                AND a.xtype = b.xtype;           
        
        
        IF NOT EXISTS ( SELECT  c.name
                        FROM    sys.indexes a
                                INNER JOIN sys.index_columns b ON a.object_id = b.object_id
                                                              AND a.index_id = b.index_id
                                INNER JOIN sys.columns c ON b.column_id = c.column_id
                                                            AND b.object_id = c.object_id
                        WHERE   c.object_id = OBJECT_ID(@tablename)
                                AND c.name = @pkname ) 
            BEGIN
				
				EXECUTE('ALTER TABLE [dbo].['+@tablename+'] ALTER COLUMN '+ @pkname +' '+ @coltype +'  NOT NULL;');
            
                EXECUTE('ALTER TABLE [dbo].['+@tablename+'] ADD CONSTRAINT [PKC_'+@tablename+'] PRIMARY KEY CLUSTERED  (['+ @pkname +']) ON [PRIMARY];')
                
                PRINT 'Added PK Sussess(table:' + @tablename + ', column:' + @pkname
            + ')'   ; 
                
            END
		ELSE 
			PRINT 'PK Already Existed(table:' + @tablename + ', column:' + @pkname
            + ')'   ; 
        RETURN 
    END;
GO

/*
test data
*/

IF EXISTS(SELECT TOP 1 1 FROM sysobjects WHERE id=OBJECT_ID('test_test') AND type='U')
DROP TABLE test_test;
GO
CREATE TABLE test_test (id UNIQUEIDENTIFIER , NAME VARCHAR(40));


EXEC proc_AddPrimaryKey 'test_test','id';