Wednesday, February 9, 2011

Metadata functions in sql server

1)
DatabasePropertyex('dbname','property') gives info about that database 
property can be like 'version','collation','status','recovery' etc
Check MSDN for all properties here:
http://msdn.microsoft.com/en-us/library/ms186823.aspx

2)
OBJECTPROPERTY ( id , property ) 
Property is like 'istable','isview','isprocedure' etc.
if true then output will be 1 else it is 0
More info at here:
'http://msdn.microsoft.com/en-us/library/ms176105%28v=SQL.90%29.aspx

3) Other easy functions are like
db_id, db_name, filegroup_id,filegroup_name,file_name,file_id,type_id,index_col

4) indexproperty(tablename,indexname,'property')
property can be like "fillfactor",'isclustered","indexdepth"

5) @@PROCID
Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger. @@PROCID cannot be specified in CLR modules or the in-process data access provider.

6)
fn_servershareddrives()
Returns the names of shared drives used by the clustered server
we can also use sys.dm_io_cluster_shared_drives instead of the above.

7)
fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } )
Returns I/O statistics for database files, including log files. 
we can also sys.dm_io_virtual_file_stats

No comments:

Post a Comment