【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) 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