SQL小结

1：查数据库所有表名

select name from sysobjects where xtype='U'

 

2：查数据库文件及可释放空间（AvailableSpaceInMB即可释放空间）

SELECT name ,size/128.0,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sysfiles;

 

3：释放文件空间

DBCC SHRINKFILE (逻辑数据文件名, 3000);

 

4：查数据库ID

select * from master.sys.sysdatabases

 

5：查当前数据库进程

select * from master.sys.sysprocesses

 

 6：列出数据库所有表的结构和表名

select * from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME

 

7：列出表中所有的索引信息

DBCC SHOWCONTIG ('TableName') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

 

8：索引

DBCC DBREINDEX(数据库名称，HRSWGDT，PK__HRSWGDT) --重建索引

DBCC INDEXDEFRAG(数据库名称，HRSWGDT，PK__HRSWGDT) --重新组织索引

 

9：获取数据库ID

SELECT DB_ID('dbname')

 

10：获取表ID

SELECT OBJECT_ID(N'tablename')

11：更新统计

select 'UPDATE STATISTICS ['+TABLE_SCHEMA+'].['+TABLE_NAME+']' from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME
 

 

 N：删除SQL SERVER记录的账户和密码

SQL Server Management Studio 2008 delete the file C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

 SQL Server Management Studio 2005 delete the file – same as above answer but the Vista path.C:\Users\%username%\AppData\Roaming \Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

 These are profile paths for Vista.
The Profile path for Windows 2003 or Xp is
C:\Documents and Settings\Administrator\Application Data\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM
If you can not find the path,you can use a tool to find the file which file name is mru.dat
At the end ,the all recold will clear as soon as you delete mru.dat