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