Microsoft SQL Server 2000 Alternative

There is a free version of Microsoft SQL Server 2000 named MSDE (Microsoft SQL Server Desktop Engine) with few limitations

Maximum 2GB database

Slow performance over 8 concurrent connections

Not supported in Windows NT 6.

No GUI management tool

http://en.wikipedia.org/wiki/MSDE

Get application name for current session in SQL server

Get application name for current session in SQL server.

SELECT APP_NAME()

Get current user name in SQL server

Get current user name in SQL server.

SELECT CURRENT_USER

Get current login name in SQL server

Get current login name in SQL server.

SELECT SYSTEM_USER

Get client IP address in SQL server

Get client machine IP address in SQL server.

DECLARE @IP_Address varchar(255)

SELECT @IP_Address = client_net_address FROM sys.dm_exec_connections WHERE Session_id = @@SPID

PRINT @IP_Address

Executing dynamic queries with more than 4000 characters in SQL server

Executing dynamic queries with more than 4000 characters in SQL server.

DECLARE @sql_Body_1 VARCHAR(8000)

DECLARE @sql_Body_2 VARCHAR(8000)

DECLARE @sql_Body_3 VARCHAR(8000)

DECLARE @sql_Body_4 VARCHAR(8000)

DECLARE @sql_Body_5 VARCHAR(8000)

DECLARE @sql_Hold VARCHAR(8000)

SET @sql_Body_1=''

SET @sql_Body_2=''

SET @sql_Body_3=''

SET @sql_Body_4=''

SET @sql_Body_5=''

SET @sql_Hold=''

DECLARE @i INT

SELECT @i = 1

WHILE @i < 1000

BEGIN

SELECT @sql_Hold = 'insert into ...'

IF 3999 - LEN(@sql_Body_1) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_1 = @sql_Body_1 + @sql_Hold

END

ELSE IF 3999 - LEN(@sql_Body_2) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_2 = @sql_Body_2 + @sql_Hold

END

ELSE IF 3999 - LEN(@sql_Body_3) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_3 = @sql_Body_3 + @sql_Hold

END

ELSE IF 3999 - LEN(@sql_Body_4) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_4 = @sql_Body_4 + @sql_Hold

END

ELSE IF 3999 - LEN(@sql_Body_5) > LEN(@sql_Hold)

BEGIN

SELECT @sql_Body_5 = @sql_Body_5 + @sql_Hold

END

SELECT @i= @i + 1

END

EXEC (@sql_Body_1+ ' ' + @sql_Body_2+ ' ' + @sql_Body_3+ ' ' + @sql_Body_4+ ' ' + @sql_Body_5)

Iterate on user tables in SQL server database

Iterate on user tables in SQL server database.

DECLARE @TABLE_NAME sysname

SELECT @TABLE_NAME= MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

WHILE @TABLE_NAME IS NOT NULL

BEGIN

PRINT @TABLE_NAME

SELECT @TABLE_NAME = MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_NAME > @TABLE_NAME

AND TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

END

Avoid trigger error for text,ntext,image data types in SQL server

SQL server triggers do not work with text,ntext and image data types. So avoid these like this...

Select COLUMN_NAME

FROM INFORMATION_SCHEMA.columns

WHERE TABLE_NAME = 'tbl_Name'

AND CAST(INFORMATION_SCHEMA.columns.DATA_TYPE AS nVARCHAR(100)) not IN (CAST('text' AS nVARCHAR(100)),CAST('ntext' AS nVARCHAR(100)),CAST('image' AS nVARCHAR(100)))

AND COLUMN_NAME NOT in

(select c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'tbl_Name'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)

Get name of non-primarykey columns of a table in SQL server

Get all column names of a table that are not primary key.

Select COLUMN_NAME

FROM INFORMATION_SCHEMA.columns

WHERE TABLE_NAME = 'tbl_Name'

AND COLUMN_NAME NOT in

(select c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'tbl_Name'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)

Get name of primarykey columns of a table in SQL server

Get names of all primarykey columns of a table in SQL server.

select c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = 'TBL_NAME'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

Replicate / Padding in SQL server

Right or left padding in SQL server. Use Replicate for this...

PRINT REPLICATE('-', 10 - LEN('TABLE'))+'TABLE'

PRINT 'TABLE' + REPLICATE('-', 10 - LEN('TABLE'))

Check and drop existing view in SQL database

Check if view exists in SQL server database and drop it if required.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TBL_View]') AND OBJECTPROPERTY(id, N'IsView') = 1)

BEGIN

DROP VIEW [dbo].[TBL_View]

END

Check and drop existing function in SQL database

Check if function exists in SQL server, and drop it if required.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FN_Name]') AND xtype in (N'FN', N'IF', N'TF'))

BEGIN

DROP FUNCTION [dbo].[FN_Name]

END

Check if table exists in SQL server database

Check if table exists or not in database in SQL server.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'TBL_Name')

BEGIN

PRINT 'Found'

END

Drop existing trigger in SQL server

Drop existing trigger from SQL server database.

IF OBJECT_ID ('Trigger_Name', 'TR') IS NOT NULL DROP TRIGGER Trigger_Name

List all user tables of a database in SQL server

List all user defined tables of a databse in SQL server.

SELECT *

FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_TYPE = 'BASE TABLE'

AND TABLE_NAME != 'sysdiagrams'

SUBSTRING in SQL server

Get substring (piece of string) in SQL server.

DECLARE @Temp AS VARCHAR(100)

SELECT @Temp = 'I am part one.I am part two.'

SELECT SUBSTRING(@Temp,1,14)

SELECT SUBSTRING(@Temp,15,CHARINDEX('.',@Temp))

IndexOf in SQL server

Find index of a character within a string in SQL server

SELECT CHARINDEX('.','I am at here.Catch me.')

Decrypt column data in SQL server

CREATE FUNCTION [dbo].[Decrypt] ( @EncryptedString NVARCHAR(4000) )

RETURNS VARCHAR(4000) WITH ENCRYPTION AS

BEGIN

DECLARE @vClearString VARCHAR(4000)

DECLARE @vIdx INT

DECLARE @vBaseIncrement INT

SET @vIdx = 1

SET @vBaseIncrement = 128

SET @vClearString = ''''

WHILE @vIdx <= LEN(@EncryptedString)

BEGIN

SET @vClearString = @vClearString +

CHAR(UNICODE(SUBSTRING(@EncryptedString, @vIdx, 1)) -

@vBaseIncrement - @vIdx + 1)

SET @vIdx = @vIdx + 1

END

RETURN @vClearString

END

To encrypt check following...

http://binarymyth.blogspot.com/2010/06/encrypt-column-data-in-sql-server.html

Encrypt column data in SQL server

CREATE FUNCTION [dbo].[Encrypt] ( @InputString VARCHAR(4000) )

RETURNS NVARCHAR(4000) WITH ENCRYPTION AS

BEGIN

DECLARE @vEncryptedString NVARCHAR(4000)

DECLARE @vIdx INT

DECLARE @vBaseIncrement INT

SET @vIdx = 1

SET @vBaseIncrement = 128

SET @vEncryptedString = ''''

WHILE @vIdx <= LEN(@InputString)

BEGIN

SET @vEncryptedString = @vEncryptedString +

NCHAR(ASCII(SUBSTRING(@InputString, @vIdx, 1)) +

@vBaseIncrement + @vIdx - 1)

SET @vIdx = @vIdx + 1

END

RETURN @vEncryptedString

END

To decrypt check following

http://binarymyth.blogspot.com/2010/06/dencrypt-column-data-in-sql-server.html

Split string/varchar in SQL server

Create function [dbo].[Split](@String nvarchar (4000),@Delimiter nvarchar (10))

returns @ValueTable table ([Value] nvarchar(4000))

begin

declare @NextString nvarchar(4000)

declare @Pos int

declare @NextPos int

declare @CommaCheck nvarchar(1)

--Initialize

set @NextString = ''

set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT

if (@CommaCheck <> @Delimiter )

begin

set @String = @String + @Delimiter

end

--Get position of first Comma

set @Pos = charindex(@Delimiter,@String)

set @NextPos = 1

--Loop while there is still a comma in the String of levels

while (@pos <> 0)

begin

set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos

set @pos = charindex(@Delimiter,@String)

end

return

END