Sequence with TSQL

We keep an unique column in every table in the database to have primary key clustered index created on it and mostly they are IDENTITY or ROWGUID. Have you ever faced a situation where you need to update a column with sequence number in an existing table or temp table? I will explain 4 different methods here to achieve the same.

SETUP:

CREATE TABLE #Employee
(Name varchar(100)
,Hiredate datetime
,Stat varchar(10))
INSERT INTO #Employee (Name, Hiredate, Stat)
VALUES ('Adams  Alex', '2001-01-01 00:00:00.000', 'Active')
, ('Brown   Barry', '2000-01-01 00:00:00.000', 'On Leave')
, ('Osako   Lee', '2009-06-11 00:00:00.000', 'Active')
, ('Kennson David', '1996-03-16 00:00:00.000', 'Has Tenure  ')
, ('Haire   Terry', '2001-11-15 00:00:00.000', 'On Leave')
, ('Smith   Sally', '1989-04-01 00:00:00.000', 'Active')
Method 01: (Using IDENTITY)

ALTER TABLE #Employee
ADD ID INT IDENTITY(1,1)
GO
SELECT * FROM #Employee
GO
--Droping the column for next examples
ALTER TABLE #Employee
drop column ID
Method 02: (Using local variable)

ALTER TABLE #Employee
ADD ID INT
GO
DECLARE @i INT = 0
UPDATE #Employee
SET @i = ID = @i + 1
SELECT * FROM #Employee
--OR--
GO
DECLARE @i INT = 0
UPDATE #Employee SET ID = NULL
UPDATE #Employee SET Id = @i , @i = @i + 1
SELECT * FROM #Employee
Method 03: (Using CTE)

UPDATE #Employee SET ID = NULL
;WITH CTE AS (
SELECT ROW_NUMBER() OVER(ORDER BY Hiredate) AS ID, Name FROM #Employee
)
UPDATE a
SET ID = b.ID
FROM #Employee a
INNER JOIN CTE b ON a.Name = b.name
SELECT * FROM #Employee
Method 04: (Using SEQUENCE)

UPDATE #Employee SET ID = NULL
GO
CREATE SEQUENCE idnum
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
GO
UPDATE #Employee
SET id = NEXT value FOR idnum
SELECT * FROM #Employee
GO
DROP SEQUENCE idnum --Droping the sequence
Posted in SQL Server

Convert rows to column using pivot

DECLARE @t AS TABLE (key1 varchar(25), [Soucecolumn Name] varchar(25), ColumnValue varchar(25)) 
INSERT INTO @t VALUES 
('8308862', 'Name', '1a234'),
('8308862', 'GrossTons', '1a235'),
('8308862', 'CallSign', '1a236'),
('8308862', 'YearofBuild','1a237'),
('8308862' ,'Type','1a238'),
('8308862','Date','1a239'),
('8308862', 'NetTons','1a245'),
('8308862','OfficialNumber','1a247'),
('8308862','PlaceofBuild','1a278'), 
 --For other key
('8308863', 'Name', '1a234'),
('8308863', 'GrossTons', '1a235'),
('8308863', 'CallSign', '1a236'),
('8308863', 'YearofBuild','1a237'),
('8308863' ,'Type','1a238'),
('8308863','Date','1a239'),
('8308863', 'NetTons','1a245'),
('8308863','OfficialNumber','1a247'),
('8308863','PlaceofBuild','1a278')

SELECT key1, [Name], [GrossTons] ,[CallSign],[YearofBuild],
[Type], [Date],[NetTons],[OfficialNumber],[PlaceofBuild]
FROM @t 
PIVOT (MAX(ColumnValue) FOR [Soucecolumn Name] IN ([Name], [GrossTons],[CallSign],[YearofBuild],
[Type], [Date],[NetTons],[OfficialNumber],[PlaceofBuild])) AS pvt
Tagged with:
Posted in SQL Server

Convert database table to c# class using tsql

TSQL script to get c# class from database tables

declare @TableName sysname = 'ImportJobTrigger'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
when 'bigint' then 'long'
when 'binary' then 'byte[]'
when 'bit' then 'bool'
when 'char' then 'string'
when 'date' then 'DateTime'
when 'datetime' then 'DateTime'
when 'datetime2' then 'DateTime'
when 'datetimeoffset' then 'DateTimeOffset'
when 'decimal' then 'decimal'
when 'float' then 'float'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'char'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'double'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'DateTime'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result + '
}'

print @Result

Posted in SQL Server

find open port on machine

You can use the NETSTAT command to quickly see all the used and listening ports on your computer. Note it is not a complete substitute for a port scanning tool. If you’d like to have your computer remotely scanned for open ports use our Security Scan instead.

To see a list of listening ports, open Command Prompt and type:

C:\> netstat -an |find /i “listening”

You can change “listening” to “established” to see what ports your computer actually communicates with.

It is also useful to use the -o switch with the NETSTAT command to also get a list of all the owning process ID associated with each connection. You can then use those process ids (PIDs) to find out the name of the processes associated with open/listening ports in the Windows Task Manager

C:\> netstat -ao |find /i “listening”

To see all open, closing, established and listening ports, simply use:

C:\> netstat -a (or netstat -ao in XP/Win2k3)

You can also make the netstat command refresh periodically by adding a number of seconds at the end, for example, to make it refresh every 5 seconds, use something like:

C:\> netstat -an 5

Posted in SQL Server

Check if database exists or not in server

Little script which checks database exists in server or not

DECLARE @dbname nvarchar(128)
SET @dbname = N’Databasename’

IF (EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE (‘[‘ + name + ‘]’ = @dbname
OR name = @dbname)))
BEGIN
SELECT ‘YES EXISTS’
END
ELSE
BEGIN
SELECT ‘NOT EXISTS’
END

Posted in SQL Server

Reset Password from asp.net membership aspnet_users

first get User Name, application Name, PasswordFormat and Salt from apsnet database.

SELECT
au.username, aa.ApplicationName, password, passwordformat, passwordsalt
FROM
aspnet_membership am
INNER JOIN
aspnet_users au ON (au.userid = am.userid)
INNER JOIN
aspnet_applications aa ON (au.applicationId = aa.applicationid)
WHERE
au.UserName = ‘jignesh.patel’

Use following script to reset password.

DECLARE @changeDate DATETIME
SET @changeDate = GETDATE()

EXEC aspnet_Membership_setPassword
‘applicationname’,
‘jignesh.patel’,
‘[password]’,
‘[passwordsalt]’,
@changeDate,
Passwordformat

Posted in SQL Server

Create SQL script for Tables in Database

select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
Tagged with: ,
Posted in SQL Server
What people say about post?
seo hizmeti on Awesome wordpress ecommerce…

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 38 other followers

%d bloggers like this: