Script to list out all DMVs and DMFs of SQL Server


-- To check the diffrent kind of system objects
SELECT Distinct type_desc
FROM sys.system_objects

-- To get the list of DMVs or DMFs
SELECT name, type, type_desc,SCHEMA_NAME(schema_id) as SNAME
FROM sys.system_objects
WHERE name LIKE 'dm[_]%'
ORDER BY name


How to generate script using SSMS GUI?

You can generate script of GUI functionality by clicking below high-lighted option in the image.

It will give you all the below options.

How to rename SA account in SQL Server

You can use below script to rename SA account. Script will work with SQL Server 2005 and higer versions.

 ALTER LOGIN sa Disable;
ALTER LOGIN sa WITH NAME = [Jugal];


New DMF for SQL Server 2008 sys.dm_fts_parser to parse a string

Check my new article on MSSQLTips

http://www.mssqltips.com/tip.asp?tip=2175

SP to recycle SQL Server and Agent Error Log file

To limit the size of the SQL Server error log, the sp_cycle_errorlog system stored procedure can be issued to start a new error log. 

To recycle SQL Server Agent log you can execute sp_cycle_agent_errorlog system stored procedure.

Issues with Using Run As to Start SQL Server Management Studio with a Different Login

http://www.mssqltips.com/tip.asp?tip=2164

Download SQL Server 2011 code name Denali

Click on below link to download Denali

http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx

How to check database ISOLATION Level?

You can use below query to check the database isolation level.

DBCC UserOptions

SQL Server Version and Code Name

SQL Server Version	Code Name
SQL Server 6.0	SQL95
SQL Server 6.5	Hydra
SQL Server 7.0	Sphinx
SQL Server 7.0 OLAP	Plato
SQL Server 2000 32-bit	Shiloh
SQL Server 2000 64-bit	Liberty
SQL Server 2005	Yukon
SQL Server 2008	Katmai
SQL Server 2008 R2	Kilimanjaro
SQL Server 2011	Denali