[转][Sql]DBA 常用语句

oracle相关对象查询：
1、查询用户表，数据行数量及分析时间
select tablespace_name,table_name,num_rows,last_analyzed from user_tables order by num_rows  desc

2、查询段名称，段类型，表空间及大小(M)，并按照大小进行排序

select segment_name,segment_type,tablespace_name,SUM (bytes) / (1024 * 1024) "TABLE_SIZE_MB" from user_segments GROUP BY
segment_name,segment_type,tablespace_name order by table_size_mb desc;


3、导出序列
select 'create sequence ' || SEQUENCE_NAME || ' minvalue ' || MIN_VALUE ||
       ' maxvalue ' || MAX_VALUE || ' start with ' || LAST_NUMBER ||
       ' increment by ' || INCREMENT_BY || ' nocache ' || ' ;'
from DBA_SEQUENCES
where SEQUENCE_OWNER = UPPER('HUBEI');--用户名


4、查询dblink

select * from dba_db_links;


5、查询索引及其字段
select i.index_name,i.status,
       i.index_type,
       i.table_owner,
       i.table_name,
       i.uniqueness,
       i.tablespace_name,
       c.column_name,
       c.column_position,
       c.column_length
from user_indexes i, user_ind_columns c
where i.index_name = c.index_name;

6、查询视图及创建语法
select view_name,text from user_views


7、查询函数
 select object_name from user_objects where object_type='FUNCTION';

8、查询数据块文件及表空间

select tablespace_name,file_name from dba_data_files;



sqlserver 相关查询

1、查询数据块文件大小及路劲（数据库名.dbo.sysfiles）

select name,filename, convert(float,size) * (8192.0/1024.0)/1024. from testgree.dbo.sysfiles

2、查询数据库用户
SELECT
name as UserName, type_desc as UserType, is_disabled as IsDisabled
FROM sys.server_principals
where type_desc in('WINDOWS_LOGIN', 'SQL_LOGIN')
order by UserType, name, IsDisabled

3、查询用户的表
--SELECT NAME FROM SYSOBJECTS WHERE TYPE='U';
select  schema_name(t.schema_id) as [Sehema],t.name as TableName,I.rows as [RowCount]
from sys.tables as t ,sysindexes as i where t.object_id=i.id and i.indid<=1

4、查询用户的索引

select  a.name as tabname
       ,h.name as idname
from  sys.objects    as  a 
right join sys.indexes  as h  on  a.object_id=h.object_id
 where  a.type<>'s' 
 



5、查询函数、存储过程
select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like'%exec%'
ORDER BY routine_type

 

转自：https://bbs.csdn.net/topics/390676806

查询 Sql Server 索引使用情况

  SELECT  @@SERVERNAME AS [ServerName] ,
        DB_NAME() AS [DatabaseName] ,
        SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName] ,
        [sObj].[name] AS [ObjectName] ,
        CASE WHEN [sObj].[type] = 'U' THEN 'Table'
             WHEN [sObj].[type] = 'V' THEN 'View'
        END AS [ObjectType] ,
        [sIdx].[index_id] AS [IndexID] ,
        ISNULL([sIdx].[name], 'N/A') AS [IndexName] ,
        CASE WHEN [sIdx].[type] = 0 THEN 'Heap'
             WHEN [sIdx].[type] = 1 THEN 'Clustered'
             WHEN [sIdx].[type] = 2 THEN 'Nonclustered'
             WHEN [sIdx].[type] = 3 THEN 'XML'
             WHEN [sIdx].[type] = 4 THEN 'Spatial'
             WHEN [sIdx].[type] = 5 THEN 'Reserved for future use'
             WHEN [sIdx].[type] = 6 THEN 'Nonclustered columnstore index'
        END AS [IndexType] ,
        [sdmvIUS].[user_seeks] AS [TotalUserSeeks] ,
        [sdmvIUS].[user_scans] AS [TotalUserScans] ,
        [sdmvIUS].[user_lookups] AS [TotalUserLookups] ,
        [sdmvIUS].[user_updates] AS [TotalUserUpdates] ,
        [sdmvIUS].[last_user_seek] AS [LastUserSeek] ,
        [sdmvIUS].[last_user_scan] AS [LastUserScan] ,
        [sdmvIUS].[last_user_lookup] AS [LastUserLookup] ,
        [sdmvIUS].[last_user_update] AS [LastUserUpdate] ,
        [sdmfIOPS].[leaf_insert_count] AS [LeafLevelInsertCount] ,
        [sdmfIOPS].[leaf_update_count] AS [LeafLevelUpdateCount] ,
        [sdmfIOPS].[leaf_delete_count] AS [LeafLevelDeleteCount]
FROM    [sys].[indexes] AS [sIdx]
        INNER JOIN [sys].[objects] AS [sObj] ON [sIdx].[object_id] = [sObj].[object_id]
        LEFT JOIN [sys].[dm_db_index_usage_stats] AS [sdmvIUS] ON [sIdx].[object_id] = [sdmvIUS].[object_id]
                                                              AND [sIdx].[index_id] = [sdmvIUS].[index_id]
                                                              AND [sdmvIUS].[database_id] = DB_ID()
        LEFT JOIN [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL,
                                                        NULL) AS [sdmfIOPS] ON [sIdx].[object_id] = [sdmfIOPS].[object_id]
                                                              AND [sIdx].[index_id] = [sdmfIOPS].[index_id]
WHERE   [sObj].[type] IN ( 'U', 'V' )         -- Look in Tables & Views
        AND [sObj].[is_ms_shipped] = 0x0   -- Exclude System Generated Objects
        AND [sIdx].[is_disabled] = 0x0
        AND sObj.name = '表名'