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)
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 cursor1 cursor
for 
select name from sysobjects where xtype=u

open cursor1
fetch next from cursor1 into @tablename

while @@fetch_status = 0
begin
set @sql = insert into tablespace 
set @sql = @sql + exec sp_spaceused + @tablename + 
exec (@sql)
fetch next from cursor1 into @tablename
end
close cursor1
deallocate cursor1
go


--显示结果
select * from tablespace
--order by tablename
--order by tablename asc --按表名称,用于统计表
--order by rowscount desc --按行数量,用于查看表行数
--order by reserved desc, data desc --按占用空间
--order by index_size desc, reserved desc --按索引空间查看
go

--查看库的使用状况,可以随时执行的。
--exec sp_spaceused
--go

 

 ····

 

 

高级功能: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] '想要查找的值'

 

 

  

 

 

 

 

 

 

```

posted @ 2021-04-20 20:48  亟待!  阅读(2324)  评论(0编辑  收藏  举报
……