SQL Serverメタデータ照会

1.クエリートリガーの情報

--        
select name,           --     
       (select name from sys.objects 
        where object_id = t.parent_id)as tb_name  --    
from sys.triggers t


--        
select *
from sys.sql_modules
where object_id = object_id('      ')

2、大総合

--    ，       
select *
from sys.objects




select *
from sys.views


select *
from sys.procedures

select *
from sys.triggers





-- 
select *
from sys.tables

-- 
select *
from sys.columns


--    
select *
from sys.types


--    
select *
from sys.key_constraints


--    
select *
from sys.foreign_keys


select*
from sys.foreign_key_columns


--default  
select*
from sys.default_constraints


--check  
select *
from sys.check_constraints


--identity 
select*
from sys.identity_columns


--   
select *
from sys.computed_columns




--  
select *
from sys.indexes


--   
select *
from sys.index_columns


--       
select *
from sys.dm_db_index_usage_stats


--       
select *
from sys.dm_db_index_operational_stats(
DB_ID('    '),
OBJECT_ID('db.dbo.object'),
null,
null,
null)


--       
select *
from sys.dm_db_index_physical_stats(
DB_ID('    '),
OBJECT_ID('db.dbo.object'),
null,
null)



--       
select *
from sys.dm_exec_sessions

select*
from sys.dm_exec_connections

select*
from sys.dm_exec_requests

select *
from sys.dm_exec_query_stats

select *
from sys.dm_exec_sql_text('sql_handle')


select*
from sys.dm_exec_query_plan('query_plan')

select *
from sys.dm_exec_plan_attributes('query_plan')



--sql    
select *
from sys.dm_exec_cached_plans


--          SQL               
dbcc freeproccache


--     Microsoft SQL Server                        
dbcc freesessioncache


--                                      
dbcc freesystemcache('all')




--database  
select *
from sys.dm_os_buffer_descriptors


--  database  
dbcc dropcleanbuffers

--    
select *
from sys.dm_os_memory_clerks



--  
select *
from sys.dm_tran_session_transactions

--     
select *
from sys.dm_tran_active_transactions

--     
select *
from sys.dm_tran_database_transactions


-- 
select *
from sys.dm_tran_locks


select *
from sys.dm_tran_current_transaction



--  
select *
from sys.dm_os_wait_stats


--     task
select *
from sys.dm_os_waiting_tasks



--tempdb       
select *
from sys.dm_db_file_space_usage

select *
from sys.dm_db_session_space_usage

select *
from sys.dm_db_task_space_usage



--  
select *
from sys.databases

select *
from sys.database_files

select *
from sys.master_files



select *
from sys.dm_io_virtual_file_stats(
									DB_ID(),
									1
								 );

select *
from sys.dm_io_pending_io_requests


--
select work_queue_count,
       pending_disk_io_count,
       failed_to_create_worker,
       *
from sys.dm_os_schedulers




--  
backup database master
to disk = 'c:\master.bak'
with format


--  
--     master    ，             。
--              ，         "     SQL Server   (sqlservr.exe)"。
restore database master
from disk = 'c:\master.bak'
with replace  --       




--   io  
set statistics io on


--   cpu  
set statistics time on


--        ，    
set statistics profile on



--DBCC        
dbcc checkdb('master')




--      

-- sql server   
SELECT * 
FROM 
OPENROWSET('SQLOLEDB', 
           'server=192.168.1.16,1433;uid=sa;pwd=winchannel', --   
           henkel.dbo.mdm_store)                           --       
           

-- Excel   ，    4.0  
select *
from 
openrowset('microsoft.jet.oledb.4.0',
           'Excel 5.0;database=c:\t2.xls', 
           
            sheet1$)          
            

--excel,12.0  
select *
from 
opendatasource('microsoft.ace.oledb.12.0',
               'data source=c:\t.xls;Extended Properties=Excel 12.0')...[sheet1$]