SqlServer数据库中表结构最后更新时间
SqlServer数据库中表结构最后更新时间
``
Sql Server查询数据的所有表名和行数及空间占用量
//查询所有表名 select name from sysobjects where xtype='u' --modify_date指表结构最后更新日期，并非数据最后更新日期
//查询所有表名及统计信息（表结构创建及最后修改时间）
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published,
is_schema_published, lob_data_space_id, filestream_data_space_id, max_column_id_used, lock_on_bulk_load, uses_ansi_nulls, is_replicated,
has_replication_filter, is_merge_published, is_sync_tran_subscribed, has_unchecked_assembly_data, text_in_row_limit,
large_value_types_out_of_row
FROM sys.tables ORDER BY modify_date DESC
//查询数据库中所有的表名及行数
SELECT a.name, b.rows
FROM sys.sysobjects AS a INNER JOIN
sys.sysindexes AS b ON a.id = b.id
WHERE (b.indid IN (0, 1)) AND (a.type = 'u')
ORDER BY a.name, b.rows DESC
//查询所有的标明及空间占用量\行数
SELECT OBJECT_NAME(id) AS tablename, 8 * reserved / 1024 AS reserved, RTRIM(8 * dpages) + 'kb' AS used, 8 * (reserved - dpages) / 1024 AS unused,
8 * dpages / 1024 - rows / 1024 * minlen / 1024 AS free
FROM sys.sysindexes
WHERE (indid = 1)
ORDER BY tablename, reserved DESC
//查询数据库中的所有数据库名
SELECT Name FROM Master..SysDatabases ORDER BY Name
//查询某个数据库中所有的表名
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
//获取一个数据库中的所有表的名称、一个表中所有字段的名称
//获取一个数据库中的所有表的名称、一个表中所有字段的名称 SELECT (case when a.colorder=1 then d.name else null end) 表名, a.colorder 字段序号,a.name 字段名, (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识, (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空, isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明] FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id left join sys.extended_properties f on d.id=f.class and f.minor_id=0 where b.name is not null --WHERE d.name='[要查询的表]' --如果只查询指定表,加上此条件 order by a.id,a.colorder
SQLSERVER数据库中查看过去一个月之内执行过的update 语句
--SQLSERVER数据库中如何查看过去一个月之内执行过的update语句 SELECT d.plan_handle , d.sql_handle , e.text FROM sys.dm_exec_query_stats d CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e
SqlServer查询数据库中每张表的数据量
在数据库量过大的时候，会查询数据库表的数据量，看看是否需要分表使用。如何查询一个数据库中所有的表及表的数据量呢？
SELECT a.name TableName,b.rows TableCount FROM sysobjects a INNER JOIN sysindexes b ON a.id=b.id WHERE b.indid IN(0,1) AND a.Type='U' and a.name not in ('') ORDER BY TableCount DESC
这样一段sql就可以查出数据库中每张表的数据量，如图：
扩展：
有的时间我想把数据库中表的记录统计一下，如果我们一个一个表的操作可以直接select count(*) from tablename就可以然后一个个相加，但是如果有上百个表有没有更简单的方法呢,下面我总结了一些方法有需要的朋友可参考。
如果是要得到中所有表的条数呢？我们来看几种最常见的方式：
--方法一
| 代码如下 | |
|
b.name as tablename , c.row_count as datacount from sys.indexes a , sys.objects b , sys.dm_db_partition_stats c where a.[object_id] = b.[object_id] AND b.[object_id] = c.[object_id] AND a.index_id = c.index_id AND a.index_id < 2 AND b.is_ms_shipped = 0 |
|
--方法二
| 代码如下 | |
|
select b.name as tablename , a.rowcnt as datacount from sysindexes a , sysobjects b where a.id = b.id and a.indid < 2 and objectproperty(b.id, 'IsMSShipped') = 0 |
|
--方法三
| 代码如下 | |
| if exists ( select * from dbo.sysobjects where id = object_id(N'[dbo].[TableSpace]') and objectproperty(id, N'IsUserTable') = 1 ) drop table [dbo].[TableSpace] go create table TableSpace ( TableName varchar(20) , RowsCount char(11) , Reserved varchar(18) , Data varchar(18) , Index_size varchar(18) , Unused varchar(18) ) go declare @sql varchar(500) declare @TableName varchar(20) declare mCursor cursor for select name from sysobjects where xtype='U' open mCursor fetch NEXT from mCursor into @TableName while @@fetch_status = 0 begin set @sql = 'insert into TableSpace ' set @sql = @sql + ' exec sp_spaceused ''' + @TableName + ''' ' exec (@sql) fetch NEXT from mCursor into @TableName end close mCursor deallocate mCursor go --显示结果 select TableName,RowsCount from TableSpace |
|
--建议使用后两种方式，对于SQL SERVER 2005来说，三种方法都好使，如果是其他板本，可以逐一测试一下。
方法四
--==========================================================================
-- 说明： 本脚本用于查询当前中所有表格的记录条数
-- 并将结果存入tableinfo表中，不会删除以备用户再做处理与分析
-- 不过，最后请用户删除此表。
--==========================================================================
| 代码如下 | |
|
if exists (select * from dbo.sysobjects where id = object_id(n[dbo].[tablespace]) and objectproperty(id, nisusertable) = 1) create table tablespace declare @sql varchar(500) declare cursor1 cursor open cursor1 while @@fetch_status = 0
--查看库的使用状况,可以随时执行的。 |
|
····
高级功能：SQL Server查询整个数据库中某个特定值所在的表和字段的方法
--SQL SERVER查询整个数据库中某个特定值所在的表和字段 CREATE PROCEDURE [dbo].[SP_FindValueInDB] ( @value VARCHAR (1024) ) AS BEGIN SET NOCOUNT ON ; DECLARE @sql VARCHAR (1024) DECLARE @table VARCHAR (64) DECLARE @column VARCHAR (64) CREATE TABLE #t ( tablename VARCHAR (64), columnname VARCHAR (64) ) DECLARE TABLES CURSOR FOR SELECT o. name , c. name FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) ORDER BY o. name , c. name OPEN TABLES FETCH NEXT FROM TABLES INTO @table , @column WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'IF EXISTS(SELECT NULL FROM [' +@table+ '] ' SET @sql = @sql + ' WHERE RTRIM(LTRIM([' +@column+ '])) LIKE ''%' +@value+ '%'') ' SET @sql = @sql + ' INSERT INTO #t VALUES (''' +@table+ ''',''' SET @sql = @sql + @column + ' '') ' EXEC (@sql) FETCH NEXT FROM TABLES INTO @table , @column END CLOSE TABLES DEALLOCATE TABLES SELECT * FROM #t DROP TABLE #t End
--只需要传入一个想要查找的值，即可查询出这个值所在的表和字段名。
exec dbo.[SP_FindValueInDB] '想要查找的值'
```
