【MSSQL】查询数据库中所有没有加主键的表，并生成加主键SQL语句

lanwd

查询数据库中所有没有加主键的表，并生成加主键SQL语句

DECLARE @tableName NVARCHAR(200) ,@SQL Nvarchar(2000);
DECLARE cur CURSOR FOR
    SELECT name from (
        SELECT name FROM sys.tables
        EXCEPT
        SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
        WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
    ) as a
    -- 根据自己项目需要，我们项目所有表都是"Sys_"开头
     WHERE  a.name like 'Sys_%' 
OPEN cur
FETCH NEXT FROM cur INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN 
  set @SQL=' IF NOT EXISTS(SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='''+@tableName+''')
BEGIN
   ALTER TABLE '+@tableName+'  ADD CONSTRAINT pk_'+@tableName+'_id PRIMARY KEY(F_Id); 
END ';
  print(@SQL);
  FETCH NEXT FROM cur INTO @tableName
END
CLOSE cur ;
DEALLOCATE cur ;