SQL 程式設計參考資源4 Schema相關

[SQL]取得資料庫中各Table的使用量資訊
使用GridView顯示Sql資料庫的Table Layout(Schema)
取得DB裡所有Table Name
[ASP.NET]利用Stored Procedures sp_MSforeachtable來取得DB所有Table的筆數
ASP.NET利用SQL指令取得MS SQL資料庫裡所有Table的名稱
取得DB裡所有Table Name
利用SQL指令找出資料庫的資料表,資料表的欄位名,資料表的PK欄位


SQL Server Management Studio 表格設計技巧

在 SQL Server 2005 中取得所有欄位定義的方法(含備註欄位)

Data Dictionary Creator
如何編輯 SQL Server 中物件的擴充屬性(Extended Property)

---

OBJECT_ID (Transact-SQL)
OBJECT_NAME (Transact-SQL)
sys.objects (Transact-SQL)

使用資料庫物件的擴充屬性
檢視擴充屬性
使用者結構描述分隔
Fun with Extended Properties in SQL Server 2008

--http://www.dotblogs.com.tw/puma/archive/2008/06/18/4326.aspx
--取得TABLE
select TABLE_CATALOG
  , TABLE_SCHEMA
  , TABLE_NAME
  , TABLE_TYPE
from
 INFORMATION_SCHEMA.TABLES
where
 (TABLE_TYPE = 'BASE TABLE')
 and 
 (TABLE_SCHEMA = 'dbo')
--取得TABLE的欄位定義
select ORDINAL_POSITION
  , TABLE_SCHEMA
  , COLUMN_NAME
  , DATA_TYPE
  , CHARACTER_MAXIMUM_LENGTH
  , NUMERIC_PRECISION
  , NUMERIC_SCALE
  , IS_NULLABLE
  , COLUMN_DEFAULT
from
 INFORMATION_SCHEMA.COLUMNS
where
 TABLE_SCHEMA = 'dbo'
 and TABLE_NAME = 'TABLE1'
--取得TABLE的PK
select CONSTRAINT_NAME
  , ORDINAL_POSITION
  , COLUMN_NAME
from
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
  TABLE_SCHEMA = 'dbo'
  and TABLE_NAME = 'Table1'

--add Description
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'流水號' , 
@level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'Table1', 
@level2type=N'COLUMN',@level2name=N'SN';

--http://sqlserverperformance.wordpress.com/2011/01/10/fun-with-extended-properties-in-sql-server-2008/
select OBJECT_NAME(MAJOR_ID) TABLE_NAME, MINOR_ID, VALUE
from SYS.EXTENDED_PROPERTIES
where NAME = N'MS_Description';

--http://angeson1987.blog.163.com/blog/static/1625900902011225114024436/
select SYS.SYSCOLUMNS.NAME, SYS.EXTENDED_PROPERTIES.value
from SYS.EXTENDED_PROPERTIES
 left outer join SYS.SYSCOLUMNS
 on SYS.EXTENDED_PROPERTIES.MAJOR_ID = SYS.SYSCOLUMNS.ID
 and SYS.EXTENDED_PROPERTIES.MINOR_ID = SYS.SYSCOLUMNS.COLID
where
 SYS.EXTENDED_PROPERTIES.NAME = N'MS_Description'
 and SYS.EXTENDED_PROPERTIES.MINOR_ID <> 0
 and OBJECT_NAME(SYS.EXTENDED_PROPERTIES.MAJOR_ID) = N'TABLE1'
 and SYS.SYSCOLUMNS.NAME = N'COLUMN1';

--test
select ORDINAL_POSITION
  , TABLE_SCHEMA
  , COLUMN_NAME
  , DATA_TYPE
  , CHARACTER_MAXIMUM_LENGTH
  , NUMERIC_PRECISION
  , NUMERIC_SCALE
  , IS_NULLABLE
  , COLUMN_DEFAULT
  , (
select CAST(Value AS NVARCHAR(200))
from SYS.EXTENDED_PROPERTIES
where NAME = N'MS_Description' 
and CLASS_DESC = 'OBJECT_OR_COLUMN'
and MAJOR_ID = OBJECT_ID(TAB.TABLE_NAME)
and MINOR_ID = TAB.ORDINAL_POSITION
  ) 'COLUMN_DESC'
from
 INFORMATION_SCHEMA.COLUMNS TAB
where
 TABLE_SCHEMA = 'dbo'
 and TABLE_NAME = 'SSS_APPLICATION'

---

SSMS -- Backup Database Object to T-SQL file step
[SQL Server]使用SQL Server Management Studio內建的功能匯出資料庫SQL Script(含資料)
SQL Server Hosting Toolkit
Microsoft SQL Server Database Publishing Wizard 1.1
SQL Server Database Publishing Wizard 1.1
[Visual Studio]使用Visual Studio搭配Microsoft SQL Server Database Publishing Wizard進行資料庫的匯出工作


Database Modeling Excel *****