红星的个人主页
0%

Posted on Edited on

sql server

找出有主键的

1
2
3
4
5
6
7
8
9
USE cwbasehwjd;  -- 切换到目标数据库

SELECT 
    CONSTRAINT_NAME,  -- 获取主键的名称
    TABLE_NAME        -- 获取主键所在的表名
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS  -- 查询表约束信息
WHERE 
 CONSTRAINT_TYPE = 'PRIMARY KEY';  -- 只筛选主键

找出没有主键的表

1
2
3
4
5
6
7
USE cwbasehwjd;  -- 切换到目标数据库

 
 SELECT name FROM sys.tables
EXCEPT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

ms做法

1
2
3
4
5
6
7
8
9
10
11
12
13
USE [cwbasehwjd];  
GO  
SELECT SCHEMA_NAME(t.schema_id) AS schema_name  
    ,t.name AS table_name  
FROM sys.tables t   
WHERE object_id NOT IN   
   (  
    SELECT parent_object_id   
    FROM sys.key_constraints   
    WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'  
    );  
GO

脚本寻找

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
USE cwbasehwjd;  -- 切换到目标数据库

declare @TableName nvarchar(250)--游标中取出的数据表名
declare @AllTableHasPrimaryKey int--是否全部都有主键
set @AllTableHasPrimaryKey=1
--声明读取数据库所有数据表名和编号的游标
declare mycursor cursor for select name from dbo.SysObjects WHERE OBJECTPROPERTY(ID, 'IsUserTable') = 1 order by name
 --打开游标
open mycursor
--从游标里取出数据赋值到我们刚才声明的数据表名变量中
fetch next from mycursor into @TableName
--如果游标执行成功      
while (@@fetch_status=0)
begin
 
 --判断当前数据表是否存在主键
 IF NOT EXISTS (select * from information_schema.key_column_usage where TABLE_NAME=''+@TableName+'')
 begin
 set @AllTableHasPrimaryKey=0;
 print '当前数据表['+@TableName+']没有主键'  
 end
 
 --用游标去取下一条记录   
    fetch next from mycursor into @TableName
end
 
if(@AllTableHasPrimaryKey=1)
begin
print '数据库中所有数据表都有主键'
end
 
--关闭游标
close mycursor
--撤销游标
deallocate mycursor
--脚本代码结束

查看每个表占用空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SELECT  db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
,
    t.create_date AS CreatedDate,
 t.modify_date AS LastModifiedDate
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 0
GROUP BY 
    t.Name, s.Name, p.Rows,t.create_date,t.modify_date
ORDER BY 
    总共占用空间MB desc

查看每个表的修改时间

1
2
3
4
5
6
7
SELECT 
    name AS TableName,
    create_date AS CreatedDate,
    modify_date AS LastModifiedDate
FROM 
    sys.tables
    order by LastModifiedDate asc

问题：在同一个sql中，将一个数据库如test备份后，新建一个数据库用test备份文件进行覆盖还原，待还原成功的时候，test数据库一直显示“正在还原”。 在这种状态下，由于未提交的事务没有回滚，导致数据库不可以访问。

解决：新建查询，执行restore database XXX with recovery语句后正常 – XXX代表数据库名称