找出資料庫中自動編號的欄位名稱與資料表名稱

最近在做資料庫的移轉，因為資料庫中的東綁西綁的，導致無法很簡單的完成，最後只好土法煉鋼，先將結構建立好後，然後用INSERT INTO去把資料轉過去，討厭的是還有很多自動編號的欄位會造成錯誤，這部份就只好特別挑出來處理，首先就要先找出有哪些Table中有自動編號的欄位了

SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)  AS TableName,
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS 'DataType',
  IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) AS CurrentIdentityValue,
  CASE c.DATA_TYPE
   WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807
   WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 2147483647
   WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 32767
   WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 255
  END AS 'PercentageUsed'
 FROM INFORMATION_SCHEMA.COLUMNS AS c
  INNER JOIN
  INFORMATION_SCHEMA.TABLES AS t
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
 WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1

出處：http://wushinetlife.blogspot.tw/2012/03/blog-post_15.html