How-to remove duplicates from MSSQL table ...

. 

-- START === 1.0.dbo..DuplicatesTable.TableCreate.sql

/****** Object:  Table [dbo].[DuplicatesTable]    Script Date: 03/29/2010 21:24:02 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[DuplicatesTable]') AND type in (N'U'))

DROP TABLE [dbo].[DuplicatesTable]

GO

/****** Object:  Table [dbo].[DuplicatesTable]    Script Date: 03/29/2010 21:24:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[DuplicatesTable](

[ColA] [varchar](10) NOT NULL, -- the name of the DuplicatesTable

[ColB] [varchar](10) NULL,  -- the description of the e DuplicatesTable 

) 

/* 

<doc> 

Models a DuplicatesTable for 

</doc>

*/

GO

--============================================================ DuplicatesTable START

declare @ScriptFileName varchar(2000)

SELECT @ScriptFileName = '$(ScriptFileName)'

SELECT @ScriptFileName + ' --- DuplicatesTable START =========================================' 

declare @TableName varchar(200)

select @TableName = 'DuplicatesTable'

SELECT 'SELECT name from sys.tables where name =''' + @TableName + ''''

SELECT name from sys.tables 

where name = @TableName

DECLARE @TableCount INT 

SELECT @TableCount  = COUNT(name ) from sys.tables 

where name =@TableName

if @TableCount=1

SELECT ' DuplicatesTable PASSED. The Table ' + @TableName + ' EXISTS ' 

ELSE 

SELECT ' DuplicatesTable FAILED. The Table ' + @TableName + ' DOES NOT EXIST ' 

SELECT @ScriptFileName + ' --- DuplicatesTable END =========================================' 

--============================================================ DuplicatesTable END

GO

-- END ===  1.0.dbo..DuplicatesTable.TableCreate.sql

. 

-- START === 1.1..dbo..DuplicatesTable.TableInsert.sql

BEGIN TRANSACTION;

INSERT INTO [dbo].[DuplicatesTable]([ColA], [ColB])

SELECT   N'ColA', N'ColB' UNION ALL

SELECT N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA', N'ColB' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1' UNION ALL

SELECT  N'ColA1', N'ColB1'

COMMIT;

RAISERROR (N'[dbo].[DuplicatesTable]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

GO

-- END ===  1.1..dbo..DuplicatesTable.TableInsert.sql

. 

-- START === 2.0.RemoveDuplicates.Script.sql

ALTER TABLE dbo.DuplicatesTable ADD

DuplicatesTableId int NOT NULL IDENTITY (1, 1)

GO

-- Then the delete is trivial:

DELETE FROM dbo.DuplicatesTable WHERE DuplicatesTableId NOT IN 

(SELECT MAX(DuplicatesTableId) FROM dbo.DuplicatesTable GROUP BY ColA , ColB)

Select * from DuplicatesTable ;  

-- END ===  2.0.RemoveDuplicates.Script.sql

How-to remove duplicates from MSSQL server

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.0..dbo..DuplicatesTable.TableCreate.sql

/****** Object:  Table [dbo].[DuplicatesTable]    Script Date: 03/29/2010 21:24:02 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[DuplicatesTable]') AND type in (N'U'))

DROP TABLE [dbo].[DuplicatesTable]

GO

/****** Object:  Table [dbo].[DuplicatesTable]    Script Date: 03/29/2010 21:24:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[DuplicatesTable](

[DuplicatesTableId] [bigint] IDENTITY(1,1) NOT NULL, --the PK  for DuplicatesTable

[ColA] [varchar](10) NOT NULL, -- the name of the DuplicatesTable

[ColB] [varchar](10) NULL,  -- the description of the e DuplicatesTable 

CONSTRAINT [PK_DuplicatesTable] PRIMARY KEY CLUSTERED 

(

[DuplicatesTableId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

/* 

<doc> 

Models a DuplicatesTable for 

</doc>

*/

GO

--============================================================ DuplicatesTable START

declare @ScriptFileName varchar(2000)

SELECT @ScriptFileName = '$(ScriptFileName)'

SELECT @ScriptFileName + ' --- DuplicatesTable START =========================================' 

declare @TableName varchar(200)

select @TableName = 'DuplicatesTable'

SELECT 'SELECT name from sys.tables where name =''' + @TableName + ''''

SELECT name from sys.tables 

where name = @TableName

DECLARE @TableCount INT 

SELECT @TableCount  = COUNT(name ) from sys.tables 

where name =@TableName

if @TableCount=1

SELECT ' DuplicatesTable PASSED. The Table ' + @TableName + ' EXISTS ' 

ELSE 

SELECT ' DuplicatesTable FAILED. The Table ' + @TableName + ' DOES NOT EXIST ' 

SELECT @ScriptFileName + ' --- DuplicatesTable END =========================================' 

--============================================================ DuplicatesTable END

GO

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.0..dbo..DuplicatesTable.TableCreate.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.1..dbo..DuplicatesTable.TableInsert.sql

SET NOCOUNT ON;

SET XACT_ABORT ON;

GO

SET IDENTITY_INSERT [dbo].[DuplicatesTable] ON;

BEGIN TRANSACTION;

INSERT INTO [dbo].[DuplicatesTable]([DuplicatesTableId], [ColA], [ColB])

SELECT 1, N'ColA', N'ColB' UNION ALL

SELECT 2, N'ColA', N'ColB' UNION ALL

SELECT 3, N'ColA', N'ColB' UNION ALL

SELECT 4, N'ColA', N'ColB' UNION ALL

SELECT 5, N'ColA', N'ColB' UNION ALL

SELECT 6, N'ColA', N'ColB' UNION ALL

SELECT 7, N'ColA', N'ColB' UNION ALL

SELECT 8, N'ColA1', N'ColB1' UNION ALL

SELECT 9, N'ColA1', N'ColB1' UNION ALL

SELECT 10, N'ColA1', N'ColB1' UNION ALL

SELECT 11, N'ColA1', N'ColB1' UNION ALL

SELECT 12, N'ColA1', N'ColB1' UNION ALL

SELECT 13, N'ColA1', N'ColB1' UNION ALL

SELECT 14, N'ColA1', N'ColB1'

COMMIT;

RAISERROR (N'[dbo].[DuplicatesTable]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

GO

SET IDENTITY_INSERT [dbo].[DuplicatesTable] OFF; 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.1..dbo..DuplicatesTable.TableInsert.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\2.0.RemoveDuplicates.Script.sql

SELECT 'SELECT * FROM DuplicatesTable ; '

SELECT * FROM DuplicatesTable ; 

Drop TABLE NonDuplicatesTable ; 

SELECT ColA , ColB 

into NonDuplicatesTable 

from DuplicatesTable

Group by  ColA , ColB 

SELECT 'SELECT * FROM NonDuplicatesTable ; '

SELECT * FROM NonDuplicatesTable ; 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\2.0.RemoveDuplicates.Script.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\all.txt

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.0..dbo..DuplicatesTable.TableCreate.sql

/****** Object:  Table [dbo].[DuplicatesTable]    Script Date: 03/29/2010 21:24:02 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[DuplicatesTable]') AND type in (N'U'))

DROP TABLE [dbo].[DuplicatesTable]

GO

/****** Object:  Table [dbo].[DuplicatesTable]    Script Date: 03/29/2010 21:24:02 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[DuplicatesTable](

[DuplicatesTableId] [bigint] IDENTITY(1,1) NOT NULL, --the PK  for DuplicatesTable

[ColA] [varchar](10) NOT NULL, -- the name of the DuplicatesTable

[ColB] [varchar](10) NULL,  -- the description of the e DuplicatesTable 

CONSTRAINT [PK_DuplicatesTable] PRIMARY KEY CLUSTERED 

(

[DuplicatesTableId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

/* 

<doc> 

Models a DuplicatesTable for 

</doc>

*/

GO

--============================================================ DuplicatesTable START

declare @ScriptFileName varchar(2000)

SELECT @ScriptFileName = '$(ScriptFileName)'

SELECT @ScriptFileName + ' --- DuplicatesTable START =========================================' 

declare @TableName varchar(200)

select @TableName = 'DuplicatesTable'

SELECT 'SELECT name from sys.tables where name =''' + @TableName + ''''

SELECT name from sys.tables 

where name = @TableName

DECLARE @TableCount INT 

SELECT @TableCount  = COUNT(name ) from sys.tables 

where name =@TableName

if @TableCount=1

SELECT ' DuplicatesTable PASSED. The Table ' + @TableName + ' EXISTS ' 

ELSE 

SELECT ' DuplicatesTable FAILED. The Table ' + @TableName + ' DOES NOT EXIST ' 

SELECT @ScriptFileName + ' --- DuplicatesTable END =========================================' 

--============================================================ DuplicatesTable END

GO

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.0..dbo..DuplicatesTable.TableCreate.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.1..dbo..DuplicatesTable.TableInsert.sql

SET NOCOUNT ON;

SET XACT_ABORT ON;

GO

SET IDENTITY_INSERT [dbo].[DuplicatesTable] ON;

BEGIN TRANSACTION;

INSERT INTO [dbo].[DuplicatesTable]([DuplicatesTableId], [ColA], [ColB])

SELECT 1, N'ColA', N'ColB' UNION ALL

SELECT 2, N'ColA', N'ColB' UNION ALL

SELECT 3, N'ColA', N'ColB' UNION ALL

SELECT 4, N'ColA', N'ColB' UNION ALL

SELECT 5, N'ColA', N'ColB' UNION ALL

SELECT 6, N'ColA', N'ColB' UNION ALL

SELECT 7, N'ColA', N'ColB' UNION ALL

SELECT 8, N'ColA1', N'ColB1' UNION ALL

SELECT 9, N'ColA1', N'ColB1' UNION ALL

SELECT 10, N'ColA1', N'ColB1' UNION ALL

SELECT 11, N'ColA1', N'ColB1' UNION ALL

SELECT 12, N'ColA1', N'ColB1' UNION ALL

SELECT 13, N'ColA1', N'ColB1' UNION ALL

SELECT 14, N'ColA1', N'ColB1'

COMMIT;

RAISERROR (N'[dbo].[DuplicatesTable]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

GO

SET IDENTITY_INSERT [dbo].[DuplicatesTable] OFF; 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.1..dbo..DuplicatesTable.TableInsert.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\2.0.RemoveDuplicates.Script.sql

SELECT 'SELECT * FROM DuplicatesTable ; '

SELECT * FROM DuplicatesTable ; 

Drop TABLE NonDuplicatesTable ; 

SELECT ColA , ColB 

into NonDuplicatesTable 

from DuplicatesTable

Group by  ColA , ColB 

SELECT 'SELECT * FROM NonDuplicatesTable ; '

SELECT * FROM NonDuplicatesTable ; 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\2.0.RemoveDuplicates.Script.sql

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\all.txt

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\1.0..d 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\all.txt

. 

-- START ===== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\ConcatenateAll.bat

ECHO EMPTY THE FILE 

ECHO REMOVE THE OUTPUT FILE ALREADY ...

ECHO. >all.txt

ECHO FOR EACH LOG FILE IN THE CURRENT DIRECTORY DO CONTCATENATE IT 

ECHO IN THE ALL.TXT FILE 

for /f %%i in ('dir /s /b /a-d') do echo . >>all.txt&ECHO -- START ===== %%i>>all.txt&type %%i>>all.txt&ECHO. >>all.txt&echo -- END ================== %%i>>all.txt&ECHO. >>all.txt

PAUSE

ECHO OPEN THE ALL.TXT FILE 

all.txt 

-- END ================== D:\cas\sfw\sql\sql_dev\Install\DbName\6.Testing\4.DuplicatesRemoval\ConcatenateAll.bat

VS shortcus

F12 -- Go to Definition
Ctrl + Shift + 8 --- Moves backward in the browse history
F9 --- Seb a break
Focus Solution Explorer - Ctrl + Alt + F
Close all windows - Alt + W , L
View full screen - Alt + Shift + Enter
Stop Debugging - Shift + F5
Start Debugging - F5
Find string (regex ) in project - Shift + Ctrl + F
Find string in current document ( selection ) - Ctrl + F
Cycle trough pages - Ctrl + Tab
View Class View - Ctrl + Shift + C
Ctrl-K, Ctrl-N Moves to the next bookmark in the document
Ctrl-K, Ctrl-P Moves to the previous bookmark

and even more

how-to convigure textpad for NSIS jump to error

In TextPad Configure - Preferences -
Command: cmd.exe
Parameters: "C:\Program Files (x86)\NSIS\makensis.exe" /V4 $FileName
CaptureOutuput: yest
Regular expression to match output:
^Error in script "(.*)" on line ([0-9]*)
Registers:
File:1 , Line:2

The new Nokia tablet caught on ARM's promotion video ?!

perl generate dates ,

source:http://www.dbforums.com/perl-dbi/1609392-generating-dates-perl-cocatenating-database-field.html

use strict ;
use POSIX qw(strftime);
use Time::Local 'timelocal_nocheck';

# here are your two passed in arguments
my $startDate = '2010-09-23';
my $daysToAdd = '360';

$startDate =~ /(\d+)-(\d+)-(\d+)/;
my ($mday,$mon,$year) = ($3,$2,$1);

# convert to epoch time
my $date = timelocal_nocheck(0,0,0,
$mday-1,
$mon-1,
$year
);
# make the new filenames
for (1..$daysToAdd) {
my $NewDate = strftime('%Y%m%d',0,0,0,
(localtime($date+=86400))[3,4,5]
);
my $RunTimeStart = "$NewDate" . "000000" ;
my $RunTimeStop = "$NewDate" . "235959" ;
print "$RunTimeStart---$RunTimeStop\n" ;
} #eof for

tsql metadata functions

SELECT

DB_ID() AS Id_DefaultDB,

DB_ID('CAS_DEV') AS  Id_SpecificDb,

DB_NAME() AS Name_DefaultDb,

DB_NAME(9) AS Name_SpecificDb;

SELECT

SCHEMA_ID() AS Id_DefaultSchema,

SCHEMA_ID('DBO') AS Id_SpecificSchema,

SCHEMA_NAME() AS Name_DefaultSchema,

SCHEMA_NAME(1) AS Name_SpecificSchema;

cas_dev_sql 0.5.8 out now ...

Download project

SSMS + WIN7 Shortcuts ( update )

Start - Run - ssms --- Start ssms

Alt + Tab --- switch windows in Win
Alt + D , Table -- go to to Search box , type the search file , Tabs to move to the actual file
CTRL+SHIFT+F -- output results to a separate file
Ctrl + E -- go to search box
Alt + W , arrows -- get the tab windows in SSMS
F5 -- execute the query
F6 -- jump between the query window and the results pane and back
CTRL + T -- Results to
CTRL + D --- Results to Grid –
CTRL + SHIFT + F --- Results to File
Ctrl + R --- Show / Hide Results Pane
Ctrl + F10 --- Run to cursor command
Alt + W, W -- Display the tabs pane ... Arrows to navigate to


Source: SQL Server Management Studio Keyboard Shortcuts - SQL Server 2008 Books Online (November 2009)

cas_sql_dev 0.5.3.20100909_215832 out now

Download Project

how-to test that a table exists on MSSQL 2008

GO

--============================================================ TEST START

declare @ScriptFileName varchar(2000)

-- get the script file name from the cmd shell 

SELECT @ScriptFileName = '$(ScriptFileName)'

SELECT @ScriptFileName + ' --- TEST START =========================================' 

declare @TableName varchar(200)

select @TableName = 'WriteHereTableName'

SELECT 'SELECT name from sys.tables where name =''MetaTable'''

SELECT name from sys.tables 

where name = @TableName

DECLARE @TableCount INT 

SELECT @TableCount  = COUNT(name ) from sys.tables 

where name =@TableName

if @TableCount=1

SELECT ' TEST PASSED. The Table ' + @TableName + ' EXISTS ' 

ELSE 

SELECT ' TEST FAILED. The Table ' + @TableName + ' DOES NOT EXIST ' 

SELECT @ScriptFileName + ' --- TEST END =========================================' 

--============================================================ TEST END

GO

how-to get system info on windows

@echo off
:: GetSystemInfo.cmd
:: A simple cmd script for diagnostics - docs at the end !!!

::SET THE LOG FILE
set LOG=System_Info_for_%COMPUTERNAME%.txt

ECHO THIS SCRIPT WILL THE BASIC INFO FOR THE WINDOWS HOST IT IS RUN ON
ECHO AND WILL SAVE IT TO A FILE CALLED %LOG%
ECHO IN THE CURRENT DIRECTORY AND OPEN IT BEFORE IT CLOSES
::SLEEP FOR 3 SECONDS
ping localhost -n 6 >NUL
PAUSE
::CLEAR THE SCREEN
CLS

ECHO TODAY IS %DATE%>%LOG%
ECHO THE TIME IS %TIME%>>%LOG%
ECHO RUNING ON %COMPUTERNAME%>>%LOG%
ECHO RUNNING FROM %CD%>>%LOG%
ECHO.>>%LOG%
ECHO.>>%LOG%
ECHO =================================================>>%LOG%
ECHO LOGGING THE SYSTEM INFO ...PLEASE WAIT
ECHO SYSTEM INFO >> %LOG%
ECHO RUNNING COMMAND:SYSTEMINFO>> %LOG%
SYSTEMINFO >> %LOG%
::SLEEP FOR 3 SECONDS
for /l %%i in (1,1,3) do CLS&@echo off&ECHO LOGGING THE SYSTEM INFO ...PLEASE WAIT %%i&ping localhost -n 2 >NUL
::CLEAR THE SCREEN
CLS

ECHO.>>%LOG%
ECHO.>>%LOG%
ECHO =================================================>>%LOG%
ECHO LOGGING THE NETWORK SETTINGS ... PLEASE WAIT
ECHO THE NETWORK SETTINGS : >>%LOG%
ECHO RUNNING COMMAND:IPCONFIG /ALL >> %LOG%
IPCONFIG /ALL >>%LOG%
::SLEEP FOR 3 SECONDS
for /l %%i in (1,1,3) do @CLS&ECHO LOGGING THE NETWORK SETTINGS ... PLEASE WAIT %%i&ping localhost -n 2 >NUL&CLS


ECHO.>>%LOG%
ECHO.>>%LOG%
ECHO =================================================>>%LOG%
ECHO LOGGING THE RUNNING SERVICES
ECHO RUNNING COMMAND:NET START>> %LOG%
NET START >>%LOG%
::SLEEP FOR 3 SECONDS
for /l %%i in (1,1,3) do CLS&ECHO LOGGING THE RUNNING SERVICES %%i&ping localhost -n 2 >NUL&CLS



ECHO.>>%LOG%
ECHO.>>%LOG%
ECHO =================================================>>%LOG%
ECHO LOGGING THE RUNNING PROGRAMS
ECHO RUNNING COMMAND:NET START>> %LOG%
TASKLIST /V >>%LOG%
::SLEEP FOR 3 SECONDS
for /l %%i in (1,1,3) do CLS&ECHO LOGGING THE RUNNING PROGRAMS %%i&ping localhost -n 2 >NUL&CLS



ECHO DONE
::SLEEP FOR 3 SECONDS
for /l %%i in (1,1,3) do CLS&ECHO DONE %%i&ping localhost -n 2 >NUL&CLS

::NOW OPEN THE FILE
CMD /C %LOG%
exit

:: ============================================================================
::
:: Yordan Georgiev v.1.1.20100904_195758
:: License:BSD, Disclaimer:Do not sue me when the sh.t hits the fan !!!
::
:: ============================================================================
::
:: Tested on Win 7 OS Version 6.1.7600
::
:: ============================================================================
::
:: VERSION HISTORY
:: 1.0 --- Initial creation the system info command , logging
:: 1.1 --- Added the ipconfig /all and net start commands , improve output

MSSQL Example Table with test script

/****** Object:  Table [dbo].[ExampleTable]    Script Date: 05/02/2010 21:13:48 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[ExampleTable]') AND type in (N'U'))

DROP TABLE [dbo].[ExampleTable]

GO

/****** Object:  Table [dbo].[ExampleTable]    Script Date: 05/02/2010 21:13:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ExampleTable](

[ExampleTableID] [bigint] IDENTITY(1,1) NOT NULL,

[DESCRIP] [varchar](30) NULL,

[myvarbinary] [varbinary](1) NULL,

[mybinary] [binary] NULL,

[myimage] [image] NULL,

[myvarchar] [varchar](1) NULL,

[mymaxvarchar] [varchar](max) NULL,

[mychar] [char](1) NULL,

[mynvarchar] [nvarchar](1) NULL,

[mynchar] [nchar](1) NULL,

[mytext] [text] NULL,

[myntext] [ntext] NULL,

[myuniqueidentifier] [uniqueidentifier] NULL,

[myrowversion] [timestamp] NOT NULL,

[mybit] [bit] NULL,

[mytinyint] [tinyint] NULL,

[mysmallint] [smallint] NULL,

[myint] [int] NULL,

[mybigint] [bigint] NULL,

[mysmallmoney] [smallmoney] NULL,

[mymoney] [money] NULL,

[mynumeric] [numeric](18, 0) NULL,

[mydecimal] [decimal](18, 0) NOT NULL,

[myreal] [real] NULL,

[myfloat] [float] NULL,

[mysmalldatetime] [smalldatetime] NULL,

[mydatetime] [datetime] NULL ,

[myCalculatedColumn]  AS (case when [mydatetime]=getdate() then 'Valid' else 'EXPIRED' end),

[mysql_variant] [sql_variant] NULL ,

[myxml] [xml] NULL ,

[INSERTDT] [datetime] NULL ,

[UPDATEDDT] [datetime] NULL ,

PRIMARY KEY CLUSTERED 

(

[ExampleTableID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],

UNIQUE NONCLUSTERED 

(

[DESCRIP] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

/*

<doc> A template table use for copy paste </doc>

<version>0.2.6.20100502</version>

*/

GO

/****** Object:  Index [IX_ANOTHERExampleTable]    Script Date: 05/02/2010 21:13:48 ******/

CREATE NONCLUSTERED INDEX [IX_ANOTHERExampleTable] ON [dbo].[ExampleTable] 

(

[mytinyint] ASC,

[myvarchar] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 

IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

/****** Object:  Index [IX_ExampleTable]    Script Date: 05/02/2010 21:13:48 ******/

CREATE NONCLUSTERED INDEX [IX_ExampleTable] ON [dbo].[ExampleTable] 

(

[mydatetime] ASC,

[myvarchar] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 

IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

--EXEC sys.sp_bindrule @rulename=N'[dbo].[range_rule]', @objname=N'[dbo].[ExampleTable].[mymoney]' , @futureonly='futureonly'

--GO

ALTER TABLE [dbo].[ExampleTable]  WITH CHECK ADD FOREIGN KEY([myint])

REFERENCES [dbo].[ExampleTableREF] ([ExampleTableREFID])

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT ((42)) FOR [mybinary]

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT ('Y') FOR [mychar]

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT ((0)) FOR [mydecimal]

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT (getdate()) FOR [INSERTDT]

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT (getdate()) FOR [UPDATEDDT]

GO

GO

--============================================================ TEST START

declare @ScriptFileName varchar(2000)

-- get the script file name from the cmd shell 

SELECT @ScriptFileName = '$(ScriptFileName)'

SELECT @ScriptFileName + ' --- TEST START =========================================' 

declare @TableName varchar(200)

select @TableName = 'ExampleTable'

SELECT 'SELECT name from sys.tables where name =''MetaTable'''

SELECT name from sys.tables 

where name = @TableName

DECLARE @TableCount INT 

SELECT @TableCount  = COUNT(name ) from sys.tables 

where name =@TableName

if @TableCount=1

SELECT ' TEST PASSED. The Table ' + @TableName + ' EXISTS ' 

ELSE 

SELECT ' TEST FAILED. The Table ' + @TableName + ' DOES NOT EXIST ' 

SELECT @ScriptFileName + ' --- TEST END =========================================' 

--============================================================ TEST END

GO

how-to get system info on a windows PC

@echo off
ECHO This script gets the basic info for the Windows host it is run on

set log=System_Info_for_%COMPUTERNAME%.txt

SYSTEMINFO > %log%
ipconfig /all >>%log%


exit

nice syntax file for textpad 5.2 for cmd bat files

; TextPad syntax definitions for batch files
; Place at C:\Program Files (x86)\TextPad 5\system\doscmd.syn
; Configure - Preferences - class cmd - enable syntax highlighting
; select file from drop down , click apply

C=1

[Syntax]
Namespace1 = 6
IgnoreCase = Yes
BracketChars = ()
OperatorChars = ~+-|:?/\*><=$&^%,@
PreprocStart =
SyntaxStart =
SyntaxEnd =
CommentStart =
CommentEnd =
CommentStartAlt =
CommentEndAlt =
SingleComment = ::
SingleCommentCol =
SingleCommentAlt = ;
SingleCommentColAlt = 1
StringStart = "
StringEnd = "
StringAlt =
StringEsc =
CharStart =
CharEnd =
CharEsc =

;Commands
[Keywords 1]
ASSOC
AT
ATTRIB
BREAK
CACLS
CALL
CD
CHCP
CHDIR
CHKDSK
CLS
CMD
COLOR
COMP
COMPACT
CONVERT
COPY
DATE
DEL
DIR
DISKCOMP
DISKCOPY
DOSKEY
ECHO
@ECHO
ENABLEEXTENSIONS
ENDLOCAL
ERASE
EXIT
FC
FIND
FINDSTR
FOR
FORMAT
FTYPE
GOTO
GRAFTABL
HELP
IF
KEYB
LABEL
MD
MKDIR
MODE
MORE
MOVE
PATH
PAUSE
POPD
PRINT
PROMPT
PUSHD
RD
RECOVER
REM
REN
RENAME
REPLACE
RESTORE
RMDIR
SET
SETLOCAL
SHIFT
SORT
START
SUBST
TIME
TITLE
TREE
TYPE
VER
VERIFY
VOL
XCOPY
RUNAS
GSAR
WHERE
PSEXEC
LSRUNAS
NET
USE

;Parts of commands
[Keywords 2]
;for
DO
IN
;if
NOT
ERRORLEVEL
EXIST
EQU
NEQ
LSS
LEQ
GTR
GEQ
CMDEXTVERSION
DEFINED
;mode
CON
COM
;start
MIN
MAX
SEPARATE
SHARED
LOW
NORMAL
HIGH
REALTIME
WAIT
USER
PASSWORD
DOMAIN
REG
IMPORT
EXPORT



;verify
OTHER
;miscellaneous
nul

;Command modifiers and operators
[Keywords 3]
;for
EOL
SKIP
DELIMS
TOKENS
;mode
ON
OFF
;HS
;TG
;BAUD
;PARITY
;DATA
;STOP
;TO
;XON
;ODSR
;OCTS
;DTR
;RTS
;IDSR
;STATUS
;LPT
;SELECT
;CP
;COLS
;LINES
;RATE
;DELAY

;whatever
[Keywords 4]

Memory

How-would you code your application after 5-10 years ?!
When the round trip to db will cost you nothing ....and memory will be infinitely scalable ..

HP commercializes the memristor