【数据库】 SQL 常用语句之系统语法

时间：2022-07-15 16:07:15

【数据库】 SQL 常用语句之系统语法

1. 获取取数据库服务器上所有数据库的名字
SELECT name FROM master.dbo.sysdatabases

2. 获取取数据库服务器上所有非系统数据库的名字

SELECT [name] FROM master.dbo.sysdatabases  ORDER BY [Name]

3. 获取数据库下所有的表名

SELECT name from sys.tables

4. 获取表结构(字段名,属性)

SELECT

     C.name as [字段名],T.name as [字段类型]

     ,convert(bit,C.IsNullable) as [可否为空]

     ,convert(bit,case when exists(SELECT  FROM sysobjects where xtype='PK' and parent_obj=c.id and name in (

         SELECT name FROM sysindexes WHERE indid in(

             SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then  else  end)   as [是否主键]

     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]

     ,C.Length as [占用字节]

     ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as [长度]

     ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),) as [小数位数]

     ,ISNULL(CM.text,'') as [默认值]

     ,isnull(ETP.value,'') AS [字段描述]

     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]

FROM syscolumns C

INNER JOIN systypes T ON C.xusertype = T.xusertype

left JOIN sys.extended_properties ETP ON ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'

left join syscomments CM on C.cdefault=CM.id

WHERE C.id = object_id('IpInfo')

5. 清理 sql 日志库

Select NAME,size From sys.database_files
ALTER DATABASE DbName SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE DbName SET RECOVERY SIMPLE

USE DbName

DBCC SHRINKFILE(tableLog,,TRUNCATEONLY)

ALTER DATABASE DbName SET RECOVERY FULL WITH NO_WAIT

ALTER DATABASE DbName SET RECOVERY FULL

6. 数据库备份语句(完整备份, 一天一个备份文件)

DECLARE @time varchar()

SET @time =CONVERT(VARCHAR(),GETDATE(),) 

DECLARE @tableName varchar()

SET @tableName='tableName' 

DECLARE @name varchar()

SET @name= @tableName+'_'+@time 

DECLARE @fileName varchar()

SET @fileName= N'G:\数据库备份\'+@time+@tableName+'.bak' 

BACKUP DATABASE @tableName

TO DISK = @fileName WITH NOFORMAT,

NAME = @name,

SKIP, REWIND, NOUNLOAD, STATS =

7. 开启执行计划（检测 SQL 性能）

Ctrl + L

8. 查询执行计划
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects

WHERE sql LIKE '%Users%'  and sql not like '%syscacheobjects%'
 
 

相关文章