Nested select statement in SQL Server 使用嵌套查询
Categories: Database; Tagged with: SQL • SQL Server; @ August 7th, 2012 11:19In SQL Server, we need to alias the subquery, otherwise there will be syntax error.
SELECT name FROM (SELECT name FROM users) // Incorrect syntax near ‘)’
SELECT uTable.name FROM (SELECT name FROM users) uTable
Generate database diagrams using MS SQL Server 2008
Categories: Database; Tagged with: Database • SQL • SQL Server; @ August 3rd, 2012 12:24Generate database diagrams using MS SQL Server 2008
Open SQL Server Management Studio,
and explorer the databases, expand the database, and right click on the ‘Database Diagrams‘,
click ‘Create new diagram’, select the tables, and the diagrams will be generated.
see more: http://www.onlinehowto.net/create-database-diagrams-using-ms-sql-server-2008/1563
SQL Server get/list all tables in the database
Categories: Database; Tagged with: SQL • SQL Server; @ July 31st, 2012 15:16SQL Server – 2008 get all tables:
USE database
SELECT * FROM SYS.TABLES ORDER BY NAME;
Oracle: 扩充TableSpace语句
Categories: Database; Tagged with: Oracle • SQL; @ November 13th, 2011 13:31Step1: check current status and get all file path
SELECT
B.FILE_NAME FILENAME,
B.TABLESPACE_NAME TABLESPACENAME,
B.BYTES/1024/1024 SIZE_MB,
(B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 USED_MB,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) Percentage
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES
order by b.tablespace_name;
B.FILE_NAME FILENAME,
B.TABLESPACE_NAME TABLESPACENAME,
B.BYTES/1024/1024 SIZE_MB,
(B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 USED_MB,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) Percentage
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES
order by b.tablespace_name;
Step2: enlarge table space file
alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XX\USERS.DBF' resize 2000m;
DB2 V9.7 Rename Column注意事项
Categories: Database; Tagged with: DB2 • RENAME COLUMN • SQL; @ October 6th, 2010 10:01DB2需要呼叫存储过程ALTOBJ存储过程以完成重命名. 该过程将重建Table, 并恢复就有的PrimaryKey, Index信息及Table的内容.
在Rename带有Index的Column时需要额外注意, 可能先DROP掉Index, 在重建完毕后再CREATE.
问题描述:
通常情况下, 使用UI或SQL均可顺利执行该过程, 但在Rename带有Index的Column时, 会产生错误:
原因是Table重建完毕后, DB2试图恢复之前的Index – 但此时使用了Rename前的ColumnName, 于是因为找不到Column而出错.
如: Rename Table中的 Column "NAME"为"NAME2"的SQL:
CALL SYSPROC.ALTOBJ ( ‘APPLY_CONTINUE_ON_ERROR’, ‘CREATE TABLE ADMINISTRATOR.STU ( ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1, CACHE 20) , NAME2 VARCHAR (64) NOT NULL ) IN USERSPACE1 ‘, -1, ? );
AlertTable UI提示信息: 
解决方法:
1. 在进行Rename之前, 先Drop掉Column相关的Index.
2. 进行Rename操作
3. 恢复之前创建的Index – 注意使用新的Column名称创建
该方法适用于UI手工操作, 也适用于代码编写.