How to Add Identity Increment Field

This code show you how to add identity increment field to Sql  table

Sql Table

Alter Table [Table1]  Add [Column Name] int Identity(1,1) Not Null

SQL Server Date Convert Function

Definition and Usage

The CONVERT() function is a general function for converting data into a new data type.
The CONVERT() function can be used to display date/time data in different formats.

Syntax

CONVERT(data_type(length),data_to_be_converted,style)

Where data_type(length) specifies the target data type (with an optional length), data_to_be_converted contains the value to be converted, and style specifies the output format for the date/time.
The styles that can be used are:

Style ID	Style Format
100 or 0	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 or 9	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 or 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 or 20	yyyy-mm-dd hh:mi:ss(24h)
121 or 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

---

Example

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

The result would look something like this:

Nov 04 2008 11:45 PM
11-04-2008
04 Nov 08
04 Nov 2008 11:45:34:243

How to truncate the transaction log of a MS SQL database

Every wanted to truncate the transaction log of a MS SQL database? We do that all the time when moving a database from one server to the other. Remember that the transaction log can take up a LOT of space in the backup file which you don’t need when you are simply moving a database to a new server (and when you don’t have the option to simply DTS).
Open up Query Analyser and run the following, substituting real database, file and log names.

I’m using these names in the example:
Database name: [myDatabase]
Data file: [myDatabase_Data]
Log file: [myDatabase_Log]

SQL Code

Use Master

go

Backup Log [myDatabase] with truncate_only

go

Use [myDatabase]

go

DBCC shrinkfile ([myDatabase_Log], 2)

go

-- data

Use Master

go

Backup Log [myDatabase] with truncate_only

go

Use [myDatabase]

go

DBCC shrinkfile ([myDatabase_Data], 1)

go

-- Get fileid

use [myDatabase]

go

select * from sysfiles

Create Table and Insert data into new Table from Old Table

This code show you how to Create Table and Insert data into new Table from Old Table

SQL Code

SELECT       Id, Name
INTO            NewTable
FROM          OldTable
WHERE       (Id = '2')

Code Description

Create New Table Name called NewTable with field name Id & Name. Data insert from OldTable filter with ID = 2 .

Data Copy into new Table from Old Table

This code show you how to Data Copy  into new Table from Old Table

SQL Code

INSERT INTO   [Table1]
                           (Id, Name)
SELECT             OldTable AS ID, Name
WHERE             (ID = '2')

Code Description
Id & Name field Data Copy from OldTable into Table1 filter with ID = 2 .

Insert into table Fix value With Filter Values

This code show you how to insert into table fix value with filter values

SQL Code

INSERT INTO Table1
                      (Col1, Col2)
SELECT     59, Col2
FROM         Table2
WHERE     (Col1 = '56')

Code Description
Table name call Table1 & Table2 with column name Col1,Col2 . This code Insert Fix value 59 into Col1 and Col2 value in Table2 into  Table1

How to get list of views names in Sql Database

Below code show you how to get list of views names in selected SQL database.

SQL Code

Select         TABLE_CATALOG,TABLE_NAME

From          INFORMATION_SCHEMA.TABLES

Where        TABLE_TYPE = 'VIEW'

Order by    Table_Name

Try it copy & Paste on SQL Query  analyzer

Code Description

1. From          INFORMATION_SCHEMA.TABLES            -  Table name in SQL master table

2. Select         TABLE_CATALOG,TABLE_NAME               -  Field name in Information Table

3. Where        TABLE_TYPE = 'VIEW'                                 -  Find Table Type 'VIEW'
4. Order by    Table_Name                                                     -  Order Field by Table name

How to get list of table names in Sql Database

Below code show you how to get list of table names in selected SQL database.

SQL Code

Select         TABLE_CATALOG,TABLE_NAME

From          INFORMATION_SCHEMA.TABLES

Where        TABLE_TYPE = 'BASE TABLE'

Order by    Table_Name

Try it copy & Paste on SQL Query  analyzer

Code Description

1. From          INFORMATION_SCHEMA.TABLES            -  Table name in SQL master table

2. Select         TABLE_CATALOG,TABLE_NAME               -  Field name in Information Table

3. Where        TABLE_TYPE = 'BASE TABLE'                     -  Find Table Type 'Base Table'
4. Order by    Table_Name                                                     -  Order Field by Table name

How to get list of filed names in Sql table

Below code show you how to get list of field names in selected SQL database table.

SQL Code

SELECT  Table_Name, Column_Name

FROM information_schema.Columns

WHERE Table_Name = '<TableName>'

ORDER BY Table_Name, Ordinal_Position

Try it copy & Paste on SQL Query  analyzer  edit with your <TableName> 

Code Description

1. FROM information_schema.Columns             -  Table name in SQL master table

2. Select Table_Name, Column_Name               -  Field name in Information Table

3. WHERE Table_Name = '<TableName>'          -  Find your table name
4. ORDER BY Table_Name, Ordinal_Position   - Order Field by Table name and original field order