Blog Archives

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

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’,

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 + ‘

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

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

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 =

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 ‘ +

Tagged with: ,
Posted in SQL Server