SQLQuery

order by sort:

select right(c_emp_no, len(c_emp_no)-2) from tbl_staff

where left(c_emp_no,2 )=’hk’

order by left(c_emp_no,2 ), cast(right(c_emp_no, len(c_emp_no)-2) as int)

join table example:

http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx

SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS

WHERE lower(TABLE_NAME) LIKE lower(‘%tbl_navigator%’)

SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS

WHERE lower(COLUMN_NAME) LIKE lower(‘%tbl_navigator%’)

–loop 幾多

SET ROWCOUNT 0

–like join table

SELECT * FROM TABLE1 WHERE EXISTS(SELECT * FROM TABLE2)

–default value1

ALTER TABLE Persons

ALTER COLUMN City SET DEFAULT ‘SANDNES’

ALTER TABLE Persons

ALTER COLUMN City DROP DEFAULT

–default value2 drop

select * from sys.default_constraints

where parent_object_id = OBJECT_ID(@tableName)

AND type = ‘D’

AND parent_column_id = (

select column_id

from sys.columns

where

object_id = OBJECT_ID(@tableName)

and name = @clumnName

)

–check PK

SELECT

TABLE_SCHEMA,

TABLE_NAME,

CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’

ORDER BY

TABLE_SCHEMA,

TABLE_NAME

–find PK FK of table

SELECT

FK_Table = FK.TABLE_NAME,

FK_Column = CU.COLUMN_NAME,

PK_Table = PK.TABLE_NAME,

PK_Column = PT.COLUMN_NAME,

Constraint_Name = C.CONSTRAINT_NAME

FROM

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

INNER JOIN

INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

INNER JOIN

INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK

ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU

ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

INNER JOIN

( SELECT i1.TABLE_NAME, i2.COLUMN_NAME

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2

ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’

) PT

ON PT.TABLE_NAME = PK.TABLE_NAME

order by FK_Table

–find column name

SELECT *

FROM INFORMATION_SCHEMA.Columns

where COLUMN_NAME = ‘event_id’

ALTER TABLE TBL_ROLE

ADD CONSTRAINT PK_TBL_ROLE PRIMARY KEY (C_ROLE_ID)

ALTER TABLE wo

add constraint wo_FK00 FOREIGN KEY (C_PAGE_CODE) REFERENCES TBL_NAVIGATOR2(C_PAGE_CODE)

ALTER TABLE TBL_ROLE_NAVIGATOR DROP FK__TBL_ROLE___C_ROL__2C5E7C59

ALTER TABLE tbl_staff_salary DROP Constraint DF__tbl_staff__i_ot___0F824689

ALTER TABLE tbl_filter

ALTER COLUMN C_DEFAULT_VALUE nvarchar(50)

ALTER TABLE tbl_position

add b_MPFSubject bit ,

c_MPF_id nvarchar(10) ,

b_TaxSubject bit ,

b_compare_daily bit ,

b_pay_govt_rest_day bit

ALTER TABLE tbl_position

alter column b_MPFSubject bit not null,

c_MPF_id nvarchar(10) not null,

b_TaxSubject bit not null,

b_compare_daily bit not null,

b_pay_govt_rest_day bit not null

ALTER TABLE tbl_position

drop column b_MPFSubject,

c_MPF_id ,

b_TaxSubject,

b_compare_daily,

b_pay_govt_rest_day

select *,

case when c_sex = ‘M’ then ‘先生’ else ‘女士’ end as c_sir_or_Madam

from tbl_staff

where c_emp_no= "

and c_dept_id is not null

–sql sleep statement

WAITFOR DELAY ’00:00:05′

–find table have rows

SELECT o.name, i.[rows]

FROM sysindexes i INNER JOIN

sysobjects o ON o.id = i.id

WHERE (i.indid 0)

ORDER BY i.name