【MSSQL】查询数据库中所有没有加主键的表，并生成加主键SQL语句
**查询数据库中所有没有加主键的表，并生成加主键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_"开头
WHEREa.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 ;
```
页:
[1]