Alter Table Modify Column Syntax

Table customer
Column Name Data Type
First_Name char(50)
Last_Name char(50)
Address char(50)
City char(50)
Country char(25)
Birth_Date date

Our goal is to alter the data type of the "Address" column to char(100). To do this, we key in:

MySQL:
ALTER TABLE customer MODIFY Address char(100);

Oracle:
ALTER TABLE customer MODIFY Address char(100);

SQL Server:
ALTER TABLE customer ALTER COLUMN Address char(100);

http://www.1keydata.com/sql/alter-table-modify-column.html

刪除MSSQL DB裡全部的資料表

因為工作需要,需要把一個db裡的資料表刪除(因為db裡的store procedure 和view要保留,不可drop 整個db)
查了一下相關的程式,留著日後可參考


declare @cmd varchar(4000)
declare cmds cursor for
Select
'drop table [' + Table_Name + ']'
From
INFORMATION_SCHEMA.TABLES
Where
Table_Name like 'prefix%'

open cmds
while 1=1
begin
fetch cmds into @cmd
if @@fetch_status != 0 break
exec(@cmd)
end
close local
deallocate local


SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

參考自此
http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/


DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'Example of carriage return';
SET @strPrint = @strPrint + CHAR(13);
SET @strPrint = @strPrint + 'SQLAuthority.com';
PRINT @strPrint;
GO
PRINT '---------------------------------'
DECLARE @strPrint VARCHAR(100);
SET @strPrint = 'Example of new line feed';
SET @strPrint = @strPrint + CHAR(10);
SET @strPrint = @strPrint + 'SQLAuthority.com';
PRINT @strPrint;
GO

ResultSet:
Example of carriage return
SQLAuthority.com
———————————
Example of new line feed
SQLAuthority.com


SQL: Update ..select

Update ..select 的Sample


UPDATE t1 t1_alias
SET (table_name, tablespace_name) = (
SELECT table_name, tablespace_name
FROM t2 t2_alias
WHERE t1_alias.table_name = t2_alias.table_name);


隨機取得資料:各種資料庫語法

之前發現的一篇文章,以後隨時可參考
出處忘了保留,Sorry...

There are lots of ways to select a random record or row from a database table. Here are some example SQL statements that don't require additional application logic, but each database server requires different SQL syntax.


Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1
--------------------------------------
Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
--------------------------------------
Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()
--------------------------------------
Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
--------------------------------------
Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1