SQL的一些有用指令

 

1. 将TEST的日志截断，并把物理文件缩小到1MB

use test

 

BACKUP LOG test WITH TRUNCATE_ONLY

go

 

DBCC SHRINKFILE (Erp_Log,1,TRUNCATEONLY)

go

 

2. 查看当前的数据库的系统文件是什么

USE TEST

select * from sysfiles

 

3. 查询SQL SERVER系统数据

 

select

object_name(constid) 'Constraint Name',

constid 'constraint ID',

(status & 0xf)

from sysconstraints

where (status & 0xf)=2

 

(status & 0xf)

1查PK

2查UNIQUE

3查FK

4查CHECK

5查Default

 

4. 查询所有有表名，字段名

 

SELECT T.name AS TableName, C.name AS ColName, ST.name AS ColType, C.Length

FROM dbo.sysobjects T

LEFT JOIN dbo.syscolumns C ON T.id=C.id

LEFT JOIN dbo.systypes ST ON C.xtype=ST.xusertype

WHERE T.xtype='U'

ORDER BY T.name,C.colid