常用SQL总结

1、删除数据库中所有表的数据

exec sp_msforeachtable "truncate table ?"

2、删除数据库中所有的表

use 数据库名(是要删除表的所在的那个数据库的名称)

GO

declare @sql varchar(8000)

while (select count(*) from sysobjects where type='U')>0

begin SELECT @sql='drop table ' + name FROM sysobjects

WHERE (type = 'U')

ORDER BY 'drop table ' + name

exec(@sql)

end

3、获取表字段信息

select a.COLUMN_NAME,a.DATA_TYPE,b.value,c.is_identity from information_schema.COLUMNS as a 
left join sys.extended_properties as b on a.TABLE_NAME=OBJECT_NAME(b.major_id) and a.ORDINAL_POSITION=b.minor_id 
inner join sys.columns c on a.COLUMN_NAME = c.name and c.object_id = object_ID('表名') 
where a.TABLE_NAME='表名' order by a.ORDINAL_POSITION

4、清空表数据

truncate table 表名

5、清空数据库缓存

DBCC FREEPROCCACHE 
DBCC DROPCLEANBUFFERS