SQL Tips & Tricks

How to get Column Names of All Tables in a database through SQL Query / SysTables in SQL Server 2008

2011-09-05T20:12:00.000-07:00

How to list all tables and each table, loop through each column using SQL query

It is not uncommon to get the list of all column names from all available tables; at times some valuable information peeps out when one researches it. The following query will get you exactly that

select sys.syscolumns.name as ColumnName, sys.sysobjects.name as TableName from sys.syscolumns,sys.sysobjects where sys.sysobjects.id = sys.syscolumns.id

How to Split SQL String to Multiple String using Delimiter

2011-06-03T01:11:00.000-07:00

How to check if a text exists within another in SQL Server 2008 / How to split SQL Column based on Comma

You can use the combination of CharIndex and Substring function in SQL to split the string based on delimiter.

Here is a way to split a column based on Comma

Select LTRIM(SUBSTRING(EmpAddress, CHARINDEX( ',',EmpAddress)+1,len(EmpAddress))) from #TempEmployee

We have used Ltrim to remove any leading spaces

A RETURN statement with a return value cannot be used in this context. - Create Function

2011-05-31T06:35:00.000-07:00

If this error occurs in the Create Function that uses a table then the variable after return statement should be removed

The syntax should be like

Create Function
Returns Table
(
Table Definition here
..
..
)
as
BEGIN
..
..
RETURN -- note no value is returned here
END

How to get the list of Functions in Database

2011-05-31T06:06:00.000-07:00

How to Filter Functions from Sys.Objects

The following query will list of the Functions that are part of the database:

SELECT * FROM

sys.objects

where type in ('FN', 'IF', 'TF')

Cannot alter column 'X' because it is 'timestamp'.

2011-02-21T01:31:00.000-08:00

How to Change DataType of TimeStamp Column.

Datatype of Timestamp column cannot be deleted. Hence it is advised to drop the column and re-create it

For example,

ALTER TABLE [dbo].[PerformanceMaster] DROP COLUMN PerformanceDate
ALTER TABLE [dbo].[PerformanceMaster] ADD PerformanceDate datetime2

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

2011-02-21T00:59:00.000-08:00

TimeStamp Column in SQL Server

TimeStamp is used to store unique binary numbers within a database. This column is autogenerated with a storage size of 8 bytes.

SQL Server - TimeStamp Column

IF you want to store the Date and Time (e.g., updated time etc) use datetime2 datatype instead of timestamp.

SQL Server DateTime Column to Record Date and Time

The error "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." occurs when you try to insert the value using Insert statement. Leaving the column in the Insert will automatically update the value

Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, PerformanceDate )
values (@PerformanceID, @EventID, @ArtistID, @UpdateDate )

Should throw and error. Use the following instead:

Insert into PerformanceMaster (PerformanceID, EventID, ArtistID, )
values (@PerformanceID, @EventID, @ArtistID)

Information_Schema.Routines doesn't return all procedure names

2011-01-14T18:33:00.000-08:00

How to get a list of Procedures containing Specific Text / How to get a list of Procedures using Particular Table

Here is one method we generally use for returning Stored Procedures containing a particular text

SELECT ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%used_by_id%'
AND ROUTINE_TYPE = 'PROCEDURE'

order by ROUTINE_NAME

However, we found it has some problems when the text appears at the fag end of big procedures. Instead try the following also and match the result

SELECT OBJECT_NAME(id)

FROM syscomments

WHERE [text] LIKE '%used_by_id%'

AND OBJECTPROPERTY(id, 'IsProcedure') = 1

GROUP BY OBJECT_NAME(id)

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(object_id) LIKE '%used_by_id%'

order by name

SELECT OBJECT_NAME(object_id)

FROM sys.sql_modules

WHERE Definition LIKE '%used_by_id%'

AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

order by OBJECT_NAME(object_id)

How to Specify Foriegn Key Relationships in SQL Server 2008

2010-12-16T19:51:00.000-08:00

Foriegn Key in SQL Server 2008 - Use the Relationships option

Specify the relationship between the columns

Add new Relationship name and save when prompted

Unable to change Identity Specification in SQL Server 2008

2010-12-16T19:41:00.000-08:00

Unable to change Identity Specification in SQL Server 2008

How to make Server Explorer visible in Visual Studio

2010-12-16T19:37:00.000-08:00

Server Explorer not visible in Visual Studio

Your pending changes require the following tables to be dropped and re-created

2010-12-16T19:30:00.000-08:00

Remove the checkbox 'Prevent saving changes'

How to create auto-increment column in SQL Server

2010-12-16T19:26:00.000-08:00

How to Set Increment value in SQL Server

Set Column properties - goto Identity Specification, set (Is Identity) to True (Yes)

SQL Server Identity Specification

How to specify Default value for a column in SQL Server

2010-12-16T19:18:00.000-08:00

SQL Server Default Column Values

Here is a way to specify default values in Sql Server Management Studio for a column

SQL Server 2008 - Saving changes is not permitted

2010-04-05T18:36:00.000-07:00

Error Message: String or binary data would be truncated. - SQL SERVER

2010-04-05T18:16:00.000-07:00

No row was updated.

The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: String or binary data would be truncated.
The statement has been terminated.

Correct the errors and retry or press ESC to cancel the change(s).

The above error occurs in Microsoft SQL Server Management Studio when you attempt to enter more characters than the maximum level for that field

How to set Case Sensitive comparison in SQL Server 2008

2010-04-05T18:13:00.000-07:00

How to set a field as Case Sensitive in SQL Server 2008.

Here is a simple way to do that

No row was updated Error in SQL Server 2008

2010-04-05T18:11:00.000-07:00

No row was updated.
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Cannot insert the value NULL into column 'ID', table 'TeamDB1.dbo.PlayerDetails'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).

This error occurs when you try leave a Primary Key Field empty

SQL SERVER – Differences in Vulnerability between Oracle and SQL Server

2010-01-17T18:25:00.000-08:00

SQL SERVER – Differences in Vulnerability between Oracle and SQL Server

Here is a link to article by MVP Pinal Dave:

http://digg.com/software/Differences_in_Vulnerability_between_Oracle_SQL_SERVER

This article busts the myth that Oracle is better than SQL Server.

Link to original article: http://blog.sqlauthority.com/2009/12/18/sql-server-differences-in-vulnerability-between-oracle-and-sql-server/

Assign Primary Key in SQL Server Management Studio

2010-01-11T19:20:00.000-08:00

How to Create Primary Key in SQL Server Management Studio

In Table Designer, click the row selector for the database column you want to define as the primary key. If you want to select multiple columns, hold down the CTRL key while you click the row selectors for the other columns.
Right-click the row selector for the column and select Set Primary Key. A primary key index, named "PK_" followed by the table name, is automatically created; you can find it on the Indexes/Keys dialog box.

Primary Key in SQL SERVER
Primary Key Index

How to add Next and Previous Topic buttons to the SQL Server Help toolbar

2010-01-11T19:17:00.000-08:00

With SQL Server tutorials open, right-click an empty area of the toolbar, and then click Customize.
In the Customize dialog box, click the Commands tab.
In the Categories list box, click Help.
From the Commands list box, drag the Next Topic button to the desired location on the toolbar.
From the Commands list box, drag the Previous Topic button to the desired location on the toolbar.
Click Close.

SQL Server Help File

Categories Box - Help

How to set delay in SQL Server 2008 / How to Set Sleep Time in SQL Server

2009-12-23T18:53:00.000-08:00

Sleep Function in SQL Server

T-SQL Statement WaitFor Delay can be used to set the sleep time in SQL batch. The following example sets one-minute sleep time

print getdate()

waitfor delay '00:01'

print getdate()

Here is the output:

Sep 15 2009 6:43AM

Sep 15 2009 6:44AM

While Loop in SQL Server

2009-09-05T19:48:00.001-07:00

While loop in T-SQL

Here is a simple example of While loop

Declare @temp tinyint = 1

while (@temp < 11)

begin

print cast(@temp as varchar(2)) + ' X 2 = ' + cast(@temp * 2 as char)

set @temp += 1

end

The above code will be having the following output:

Output

1 X 2 = 2

2 X 2 = 4

3 X 2 = 6

4 X 2 = 8

5 X 2 = 10

6 X 2 = 12

7 X 2 = 14

8 X 2 = 16

9 X 2 = 18

10 X 2 = 20

'int' is not a recognized CURSOR option.

2009-09-05T19:47:00.001-07:00

This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table

declare #myi int

Should be replaced by

declare @myi int

Compound Operators in SQL Server 2008

2009-09-04T05:15:00.001-07:00

Compound operators execute some operation and set an original value to the result of the operation

Declare @temp tinyint

Select @temp = 1

Set @temp += @temp

print @temp

The following is a list of compound operators

Operator	Link to more information	Action
+=	+= (Add EQUALS) (Transact-SQL)	Adds some amount to the original value and sets the original value to the result.
-=	-= (Subtract EQUALS) (Transact-SQL)	Subtracts some amount from the original value and sets the original value to the result.
*=	*= (Multiply EQUALS) (Transact-SQL)	Multiplies by an amount and sets the original value to the result.
/=	/= (Divide EQUALS) (Transact-SQL)	Divides by an amount and sets the original value to the result.
%=	%= (Modulo EQUALS) (Transact-SQL)	Divides by an amount and sets the original value to the modulo.
&=	&= (Bitwise AND EQUALS) (Transact-SQL)	Performs a bitwise AND and sets the original value to the result.
^=	^= (Bitwise Exclusive OR EQUALS) (Transact-SQL)	Performs a bitwise exclusive OR and sets the original value to the result.
=	= (Bitwise OR EQUALS) (Transact-SQL)	Performs a bitwise OR and sets the original value to the result.

Date and Time Data Types in SQL Server 2008

2009-09-04T05:14:00.000-07:00

Some of the new data that are introduced in Microsoft SQL Server 2008 are :

Date
Time
Datetime2
Datetimeoffset

Here is a simple self-explanatory example:

declare @date date = getdate()

declare @time time = getdate()

declare @dt2 datetime2 = getdate()

declare @dt datetime = getdate()

declare @sdt smalldatetime = getdate()

print 'Date ' + cast(@date as char)

print 'Time ' + cast(@time as char)

print 'SmallDateTime ' + cast(@sdt as char)

print 'DateTime ' + cast(@dt as char)

print 'DateTime2 ' + cast(@dt2 as char)

Here is the output of the same:

Date 2009-09-04

Time 16:18:54.5730000

SmallDateTime Sep 4 2009 4:19PM

DateTime Sep 4 2009 4:18PM

DateTime2 2009-09-04 16:18:54.5730000