Category: T-SQL Script

Two Ways to Know the Underlying OS Version Number by T-SQL

The very quick one is select @@version. Another way is to take advantage of extended stored procedure xp_msver, for example, exec xp_msver ‘WindowsVersion’.

2011-07-04
T-SQL Script to Return the Service Accounts of SQL Server and SQL Agent Services

/* This script reads out the names of sql server service account and sql server agent service account Author:Ning Xu Date: 2010-09-16 Applied version: 2000, 2005, 2008 */ DECLARE @serviceaccount as varchar(50) EXECUTE master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SYSTEM\CurrentControlSet\Services\MSSQLSERVER’, N’ObjectName’, @serviceAccount OUTPUT, N’no_output’ SELECT @Serviceaccount as SQLServer_ServiceAccount EXECUTE master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT’, N’ObjectName’, @serviceAccount OUTPUT, N’no_output’ SELECT @serviceaccount as…

2011-03-01
T-SQL Script to Backup All Databases in an Instance

/* This script make full backups of all user databases and three system databases in a specific instance. All backup files will named as “db_name_current_date.bak”. Watch out that you have to set up the correct backup directory in the @path parameter. Author: Ning Xu Date: 22-09-2010 Applied version: 2000, 2005, 2008, 2008 R2 */ DECLARE…

2011-02-23
Remove Full Duplicates by Using row_number() Function and Common Table Expression

This blog article is inspired by another very informative one which explains really well the meaning of the new row_number() function introduced since SQL2005. Since the original article did not give a full answer to how to delete multiple fully duplicated rows in a table, I am putting my code here for the test. The good…

2011-02-21
Script that fixes the “sql server agent service won’t start” problem

/* This script solves the “sql server angent service won’t start” problem. Usually if you are pretty sure that the service account running sqlserveragent has sufficient rights and this service still refuses to start up, it is most of the time becuase of either the misconfigured “Agent XPs” or inaccessible sql server agent errorlog file.…

2011-02-18
T-SQL Script that Collects Version, Edition, sp, Authentication and Collation Info

/* This script collects version, edition, sp, authentication and collation info from the whole list of instances in the central management servers Author: Ning Xu Last Modified Date: 2014-01-21 Applied version: 2000, 2005, 2008, 2008 R2, 2012 */ select (case substring(cast(serverproperty(‘productversion’) as varchar), 1, 4) when ‘8.00’ then ‘2000’ when ‘9.00’ then ‘2005’ when ‘10.0’…

2011-02-01