首页 > 代码库 > 【SQL】 常用语句之系统语法
【SQL】 常用语句之系统语法
【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 1 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 1 else 0 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‘),0) 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 tableName SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE tableName SET RECOVERY SIMPLE
USE Social360_A
DBCC SHRINKFILE(tableLog,0,TRUNCATEONLY)
ALTER DATABASE tableName SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE tableName SET RECOVERY FULL
6. 数据库备份语句(完整备份)
DECLARE @name varchar(100)
DECLARE @name varchar(100)
SET @name= N‘db_‘+CONVERT(VARCHAR(10),GETDATE(),120)
BACKUP DATABASE [btb_shop_cangzhou]
TO DISK = N‘G:\数据库备份\db.bak‘ WITH NOFORMAT,
NAME = @name,
SKIP, REWIND, NOUNLOAD, STATS = 10
7. 开启执行计划（检测 SQL 性能）
Ctrl + L
【SQL】 常用语句之系统语法
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。