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