Search This Blog

Loading...

2010-09-29

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

2010-09-27

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

2010-09-26

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 ?!

2010-09-23

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

2010-09-21

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;

2010-09-13

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)

2010-09-05

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

2010-09-04

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]

2010-09-01

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

Labels

perl (41) Cheat Sheet (25) how-to (24) windows (13) oracle (12) sql server 2008 (12) Unix (11) linux (11) sql (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) code generation (7) cygwin (7) meta data mssql (7) metadata (7) Informatica (5) cheatsheet (5) energy (5) excel (5) tsql (5) utilities (5) bash (4) future (4) generic (4) html (4) perl modules (4) programs (4) settings (4) sh (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) sftp (3) shortcuts (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) ascii (2) awk (2) batch (2) cas (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) export (2) file (2) free programs (2) google code (2) logging (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nzsql (2) ora (2) prediction (2) publish (2) python (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) Khan Academy (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture (1) archive (1) arguments (1) avatar (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) debt (1) diagram (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) excel shortcuts (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) functions (1) fusion research (1) german (1) google cli (1) google command line interface (1) gpg (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ideas (1) image (1) informatica repo sql workflows sessions file source dir (1) informatica sql repository (1) install (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash user accounts password (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz netezza cheat sheet (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) outlook (1) parser (1) password (1) paths (1) perl disk usage administration Linux Unix (1) permissions (1) php (1) picasa (1) platform (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scp (1) scripts (1) scsi (1) sed (1) sendEmail (1) sh stub (1) silicon (1) smtp (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tool (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vim perl regex bash search for string (1) vimeo (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows programs (1) windows reinstall (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List

Loading...