一、SQL
- 在AdventureWork2012資料庫建立查詢,查詢COLUMNS、INFORMATION_SCHEMA.TABLES所有資料,並將兩段語法分別複製起來。
(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.COLUMNS)
(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.TABLES)
- 建立一個新資料庫,新增檢視表,將剛剛兩段語法分別貼上,執行後,分別儲存成vAdv2012Columns、vAdv2012Tables兩張檢視表。
- 建立新檢視表,加入vAdv2012Columns、vAdv2012Tables兩個資料表,用Table_Name做關聯。
- 開啟EXCEL,「資料」頁籤→取得外部資料→從SQL Server→伺服器名稱:.、WINDOWS驗證,下一步,選取剛剛建立的檢視表(vAdv2012AllCharColumns)→下一步→完成執行成儲存成vAdv2012AllCharColumns檢視表。
- 修改欄位名稱:
- TABLE_NAME→資料表
- COLUMN_NAME→欄位名稱
- IS_NULLABLE→NULL
- CHARACTER_MAXIMUM_LENGTH→長度
- 新增一欄:說明
二、統計Product.Color各種字元長度的資料筆數。
- 查詢Product資料表中,Color、Name、ProductNumber三個欄位的平均長度。
SELECT ‘Color’ AS 欄位名稱, avg(Len([Color])) AS 平均長度
FROM [AdventureWorks2012].[Production].[Product] union SELECT ‘Name’ AS 欄位名稱, avg(Len([Name])) AS 平均長度 FROM [AdventureWorks2012].[Production].[Product] union SELECT ‘ProductNumber’ AS 欄位名稱, avg(Len([ProductNumber])) AS 平均長度 FROM [AdventureWorks2012].[Production].[Product] |
- 用子查詢(data)的結果,依ColorLength做分組(GROUP BY),再依ColorLength做排序
SELECT ColorLength, count(*) as 資料筆數
FROM –名為data的子查詢 ( SELECT TOP 1000 [Name] ,[Color] ,Case When [Color] is Null then 0 else Len([Color]) end ColorLength FROM [AdventureWorks2012].[Production].[Product] order by Case When [Color] is Null then 0 else Len([Color]) end ) data GROUP BY ColorLength ORDER BY ColorLength |
三、查詢AdventureWorks2012資料庫中各個表格的每個欄位中的字元長度
- 把vAdv2012AllCharColumns檢視表增加Table_Catalog、vAdv2012Tables.Table_Schema兩個欄位,並移到最上面,IS_NULLABLE欄為增加篩選條件= ‘NO’。
- 將http://data.bilab.pro:999/db/Select_Min.txt的語法複製到新的查詢(語法可參考20170309_Cursor)。
Declare mydata Cursor for –因為最後cmd1的指令會超過定義的字串長度,故用top20限制資料來源筆數
SELECT top 20 dbo.vAdv2012Tables.TABLE_CATALOG, dbo.vAdv2012Tables.TABLE_SCHEMA, dbo.vAdv2012Columns.TABLE_NAME,
dbo.vAdv2012Columns.COLUMN_NAME, dbo.vAdv2012Columns.IS_NULLABLE,
dbo.vAdv2012Columns.CHARACTER_MAXIMUM_LENGTH
FROM dbo.vAdv2012Columns INNER JOIN
dbo.vAdv2012Tables ON dbo.vAdv2012Columns.TABLE_NAME = dbo.vAdv2012Tables.TABLE_NAME
WHERE (dbo.vAdv2012Columns.DATA_TYPE = N’nvarchar’) AND (dbo.vAdv2012Tables.TABLE_TYPE = ‘BASE TABLE’) AND
(dbo.vAdv2012Columns.IS_NULLABLE = ‘NO’)
declare @cmd1 nvarchar(4000)
–上面資料來源查詢了6個欄位,所以要宣告6個變數來儲存
declare @colCatalog nvarchar(100)
declare @colSchema nvarchar(100)
declare @colTable nvarchar(100)
declare @colColumn nvarchar(100)
declare @colNull nvarchar(100)
declare @colLen nvarchar(100)
Open mydata
Fetch Next from mydata into @colCatalog, @colSchema, @colTable, @colColumn, @colNull, @colLen
Declare @flag int
set @flag=0
set @cmd1=”
WHILE @@FETCH_STATUS = 0
BEGIN
IF @flag=1
begin
set @cmd1=@cmd1 + ‘ union ‘;
end
SET @cmd1=@cmd1+’ Select ”’ + @colTable + ”’ as ‘+ N’表格名稱’ + ‘, ”’+@colColumn+ ”’ as ‘+N’欄位名稱’+’, avg(Len(‘ + @colLen + ‘)) as ‘ + N’平均長度’ + ‘ from ‘ + @colCatalog + ‘.’ + @colSchema + ‘.’ + @colTable
set @flag=1
FETCH NEXT FROM mydata INTO @colCatalog, @colSchema, @colTable, @colColumn, @colNull, @colLen
END
print (@cmd1)
exec (@cmd1)
CLOSE mydata
DEALLOCATE mydata