:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Jan 23 / 2009
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

T-SQL script to list all languages and their IDs in SQL Server 2005 and SQL Server 2008

A DBA may be required LanguageID to query in system catalog and resources. For example in SQL Server 2000, to get complete default list of SQL Server error number, severity level and error description you have to use following command

/*
Get default list of SQL Server error numbers
and descriptions in SQL Server 2000
*/

USE Master
SELECT * FROM SysMessages
GO

While to get same list in SQL Server 2005 or 2008, you have to modify the command as

/*
Get default list of SQL Server error numbers
and descriptions in SQL Server 2005 and SQL Server 2008
*/

USE Master
SELECT * FROM SysMessages
WHERE MsgLangId = 1033
GO

So we have to provide the language ID in some cases. To get list of languages along with ID use following script

/*
T-SQL Script to list all languages
and their IDs in SQL Server 2005 and SQL Server 2008
*/

USE Master
SELECT MsgLangId, Alias
FROM Sys.SysLanguages
GO

LanguageList
You may further choose use full parameters from Sys.Languages table.

  • Dec 12 / 2008
  • 0
Data Modeling and Database Design, dbDigger, T-SQL Scripts

Column names in each table must be unique. Column name ‘%.*ls’ in table ‘%.*ls’ is specified more than once.

The error message
Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name ‘%.*ls’ in table ‘%.*ls’ is specified more than once.

is run time error message of severity level 16. This error message appears when you try to specify a name for a column more than once upon creation of a table. To execute the statement columns names within a table must be unique.
You may add any check through if exists. In that case you can query following meta data to check existence of any column in a table.

select * from INFORMATION_SCHEMA.COLUMNS
where table_name = 'tableName'
and column_name = 'columnName'
GO
  • Nov 21 / 2008
  • 0
Date and Time, dbDigger, SSMS tips and tricks, System Functions, T-SQL Scripts

T-SQL DateTime functions to find various dates

Recently i found a very handy collection of various T-SQL DateTime functions to find different dates. I would list those here as it as for my daily use and you may also take benefit of this handy collection.

 --Today  
 SELECT GETDATE() 'Today'  


 --Yesterday  
 SELECT  
 DATEADD(d,-1,GETDATE())  
 AS 'Yesterday'  


 --First Day of Current Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)  
 AS 'First Day of Current Week'

  
 --Last Day of Current Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)  
 AS 'Last Day of Current Week'  


 --First Day of Last Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)  
 AS 'First Day of Last Week'  


 --Last Day of Last Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)  
 AS 'Last Day of Last Week'  


 --First Day of Current Month  
 SELECT  
 DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)  
 AS 'First Day of Current Month'  


 --Last Day of Current Month  
 SELECT  
 DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))  
 AS 'Last Day of Current Month'

  
 --First Day of Last Month  
 SELECT  
 DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))  
 AS 'First Day of Last Month'  


 --Last Day of Last Month  
 SELECT  
 DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))  
 AS 'Last Day of Last Month'  


 --First Day of Current Year  
 SELECT  
 DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)  
 AS 'First Day of Current Year' 

 
 --Last Day of Current Year  
 SELECT  
 DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))  
 AS 'Last Day of Current Year'  


 --First Day of Last Year  
 SELECT  
 DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))  
 AS 'First Day of Last Year' 

 
 --Last Day of Last Year  
 SELECT  
 DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))  
 AS 'Last Day of Last Year'  
  • Nov 15 / 2008
  • 0
dbDigger, T-SQL Enhancements, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

Insert multiple rows by using a single T-SQL insert statement

Until SQL Server 2000 T-SQL, we have to use multiple insert statements for inserting multiple rows of data in a table. For SQL Server 2005 T-SQL we can insert multiple rows by using a single insert statement. And for SQL Server 2008 T-SQL we have two methods to insert multiple rows by using a single insert statement.
In following example we will go through the statements used in SQL Server 200 T-SQL, SQL Server 2005 T-SQL, SQL Server 2008 T-SQL to insert multiple rows by using single insert statement.

 --For SQL server 2000 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FirstValue',1)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('SecondValue',2)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('ThirdValue',3)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FourthValue',4)  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FifthValue',5)  
 GO  


 -- For SQL Server 2005 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 SELECT 'FirstValue' ,1  
 UNION ALL  
 SELECT 'SecondValue' ,2  
 UNION ALL  
 SELECT 'ThirdValue' ,3  
 UNION ALL  
 SELECT 'FourthValue' ,4  
 UNION ALL  
 SELECT 'FifthValue' ,5  
 GO  


 -- For SQL Server 2008 and Above  
 USE DBName  
 GO  
 INSERT INTO MyTable (col1, col2)  
 VALUES ('FirstValue',1),  
 ('SecondValue',2),  
 ('ThirdValue',3),  
 ('FourthValue',4),  
 ('FifthValue',5)  
 GO  
  • Nov 12 / 2008
  • 0
Backup and Restore, dbDigger, System Stored Procedures, T-SQL Scripts

Backup all SQL Server databases (User and system) through T-SQL

There may be some situation where you are in need to backup all SQL Server databases. If you have a number of SQL Server databases (in majority of cases) then it would be a very good idea to use T-SQL command that will backup all SQL Server databases. Do not forget to calculate available space in drive which you are going to provide as backup destination. Use following T-SQL command to backup all SQL Server databases through a single command

SP_MSforeachdb
'BACKUP DATABASE ? TO DISK = ''C:backup?.bak'' WITH INIT'

We have used system stored procedure SP_MSforeachdb to invoke the given command for all databases. T-SQL command for SQL Server backup is provided next to SP_MSforeachdb as parameter. ? in the above command specifies the database name which is going to be handled by the stored procedure SP_MSforeachdb. It will provide the name of databases automatically one by one. It is important to note that above command will backup all system databases along with user databases

  • Nov 11 / 2008
  • 0
dbDigger, Security and Permissions, T-SQL Scripts

Database roles for SQL Server databases

A login may be member of a Server role or database role. Member of database roles have permissions for that specific SQL Server database. Following script will show that which SQL Server login is member of a particular database role.

 
USE DBName  
 GO  
 SELECT  
 p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role  
 FROM sys.database_role_members roles  
 JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id  
 JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id  
 GO  
Consult us to explore the Databases. Contact us