SQL語句分享

jeffluo

由於工作中仍有許多最底層的TSQL指令操作 .
每次在寫預存程序與TSQL指令時 ,
一直在列欄位清單 , 加@ , 加欄位屬性 ... 複製、貼上~
乾脆列幾個固定指令來作處理 .

--列出資料庫中 , 各資料表別的欄位清單
SELECT tb.name,replace((SELECT c.name + ',' FROM sys.columns AS c INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID WHERE ts.OBJECT_ID = c.OBJECT_ID and tb.name=ts.name FOR XML PATH('') )+',',',,','') as co_Namm from sys.tables tb

--列出資料庫中 , 各資料表別的欄位清單 (加上欄位格式)
SELECT tb.name,replace((SELECT ' '+c.name + ' ' +( case tp.name when 'sysname' then 'nvarchar' + '(' + convert(nvarchar,(c.max_length/2)) + ')'when 'varchar' then tp.name + '(' + convert(nvarchar,(c.max_length/2)) + ')'when 'char' then tp.name + '(' + convert(nvarchar,(c.max_length/2)) + ')'when 'nvarchar' then tp.name + '(' + convert(nvarchar,(c.max_length/2)) + ')'when 'nchar' then tp.name + '(' + convert(nvarchar,(c.max_length/2)) + ')'else tp.name end) + ' ,' FROM sys.columns AS c INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID INNER JOIN systypes tp on c.user_type_id=tp.xtype WHERE ts.OBJECT_ID = c.OBJECT_ID and tb.name=ts.name FOR XML PATH('') )+',',',,','') as co_Namm from sys.tables tb

--列出資料庫中 , 各資料表別的欄位清單 (加上@)
SELECT tb.name,replace((SELECT '@'+c.name + ',' FROM sys.columns AS c INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID WHERE ts.OBJECT_ID = c.OBJECT_ID and tb.name=ts.name FOR XML PATH('') )+',',',,','') as co_Namm from sys.tables tb

--列出資料庫中 , 各資料表別的欄位清單 (加上@及欄位格式) = 預存程序用
SELECT tb.name,replace((SELECT ' @'+c.name + ' ' +( case tp.name when 'sysname' then 'nvarchar' + '(' + convert(nvarchar,(c.max_length/2)) + ')'when 'varchar' then tp.name + '(' + convert(nvarchar,(c.max_length/2)) + ')'when 'char' then tp.name + '(' + convert(nvarchar,(c.max_length/2)) + ')'when 'nvarchar' then tp.name + '(' + convert(nvarchar,(c.max_length/2)) + ')'when 'nchar' then tp.name + '(' + convert(nvarchar,(c.max_length/2)) + ')'else tp.name end) + ' ,' FROM sys.columns AS c INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID INNER JOIN systypes tp on c.user_type_id=tp.xtype WHERE ts.OBJECT_ID = c.OBJECT_ID and tb.name=ts.name FOR XML PATH('') )+',',',,','') as co_Namm from sys.tables tb

--列出資料庫中 , 各資料表別的欄位清單 (加上@及欄位名稱) = Update 用
SELECT tb.name,replace((SELECT c.name+'=@'+c.name + ',' FROM sys.columns AS c INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID WHERE ts.OBJECT_ID = c.OBJECT_ID and tb.name=ts.name FOR XML PATH('') )+',',',,','') as co_Namm from sys.tables tb

willq

好帖