日記20110423「王國密碼」

中華文化的元素：潑墨山水留下的獨白、字裡行間隱藏的真意、縱橫交錯的人生棋局。
用詩的迷離遊走在星羅夜空的擺佈裡，讓想像牽著歷史的手，合而走出創意在舞台的激情和感動。
每個人都有著故事，握著的，你能感受到它的真嗎？

Read More

Tables

顯示特定資料表的所有欄位名稱
(1)
SELECT B.NAME  FROM SYSOBJECTS AS A, SYSCOLUMNS AS B WHERE A.XTYPE = 'U' AND A.ID = B.ID AND A.NAME='TableName';

(2)
SELECT ORDINAL_POSITION,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TableName' ORDER BY ORDINAL_POSITION ASC;

(3)
EXEC SP_COLUMNS TableName;

(4)
DECLARE @mStrColumns VARCHAR(8000) , @mStrTable VARCHAR(100)
SET @mStrTable ='TABLE_NAME'

SELECT @mStrColumns = COALESCE(@mStrColumns + ', ', '') + CAST(SC.[NAME] AS VARCHAR(100) ) ,
@mStrTable = schema_Name(schema_id) + '.' + SO.NAME
FROM syscolumns sc
JOIN sys.objects so ON sc.id = so.object_ID
WHERE sc.status <> COALESCE (NULL  ,sc.status+1) AND so.name = @mStrTable

SELECT 'SELECT ' + @mStrColumns + ' FROM '  + @mStrTable

/**** Reference ****/
syscolumns
Returns one row for every column in every table and view, and a row for each parameter in a stored procedure in the database.
http://msdn.microsoft.com/en-us/library/ms186816(SQL.90).aspx

sysobjects
Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure.
http://msdn.microsoft.com/en-us/library/ms177596(SQL.100).aspx

sys.objects
Contains a row for each user-defined, schema-scoped object that is created within a database.
http://msdn.microsoft.com/en-us/library/ms190324(SQL.90).aspx

coalesce
Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.
http://msdn.microsoft.com/en-us/library/ms190349.aspx
/**** Reference End ****/

取得資料庫所有資料表名稱
SELECT * FROM SYSOBJECTS WHERE XTYPE = 'U';


伺服器各資料庫的名稱
SELECT NAME FROM MASTER.DBO.SYSDATABASES;


資料表及檢視表名稱
SELECT Table_name FROM INFORMATION_SCHEMA.TABLES ORDER BY Table_name;


找資料表的PK欄位
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName';

Read More

The log file for database is full

[交易紀錄已滿]

Microsoft OLE DB Provider for SQL Server error '80040e14'
資料庫 'OverTime' 的交易記錄已滿。如果要瞭解為何無法重複使用記錄中的空間，請參閱 sys.databases 中的 log_reuse_wait_desc 資料行。

http://support.microsoft.com/kb/317375/zh-tw

http://sharedderrick.blogspot.com/2009/03/transaction-log-for-database-is-full.html
ALTER DATABASE Northwind

SET RECOVERY [SIMPLE|FULL]

GO

Read More

[盤後觀感] 110407

     
若跌破8869
支撐：8869/8850/8828/8802/8767

若跌破8767
支撐：8712/8631

Read More