SQL Server How can specific columns be excluded from results when selecting from INFORMATION_SCHEMA?

iswrvxsc 于 2023-04-04 发布在其他

关注(0)|答案(3)|浏览(117)

Here is my previous question on how to iterate a string in SQL Server:

Now how can I specifically select column names? here is my code for selecting columns:

SELECT 'Field '+CAST(ROW_NUMBER() OVER (ORDER BY ordinal_position) AS varchar(5))+': ' + 
COLUMN_NAME
FROM information_schema.columns 
WHERE table_Name = 'SystemDefined' and table_schema = 'schemaAsset'

Here is the output:

Field 1: Asset_No
Field 2: AssetCategory
Field 3: AssetClassification
Field 4: PurchaseType
Field 5: Department
Field 6: RespPerson
Field 7: Status
Field 8: Location

This the output I want when selecting specific column names:

Field 1: Asset_No
Field 2: AssetCategory
Field 3: AssetClassification
Field 4: PurchaseType
Field 5: Department
Field 6: RespPerson
Field 7: Status

sql-server

来源：https://stackoverflow.com/questions/15102439/how-can-specific-columns-be-excluded-from-results-when-selecting-from-informatio

3条答案

按热度按时间

kq0g1dla1#

How about using NOT IN which you can use on the WHERE clause to specify for another condition.

SELECT...
FROM..
WHERE  table_Name = 'SystemDefined' AND 
       table_schema = 'schemaAsset' AND
       COLUMN_NAME NOT IN ('Status',....) --  specify the list of names you
                                          -- don't want to show

赞(0）回复(0）举报 2023-04-04

piah890a2#

Unless I am missing something, you can just use a WHERE clause to exclude the column(s) you do not want:

SELECT 'Field '
  + CAST(ROW_NUMBER() OVER (ORDER BY ordinal_position) AS varchar(5))+': ' 
  + COLUMN_NAME
FROM information_schema.columns 
WHERE table_Name = 'SystemDefined' 
  and table_schema = 'schemaAsset'
  and COLUMN_NAME <> 'Location'

If you have multiple columns, then you can use NOT IN ('Location', 'etc')

赞(0）回复(0）举报 2023-04-04

2vuwiymt3#

Recommend sys.columns instead of INFORMATION_SCHEMA ( here's why ).

SELECT 'Column ' 
  + CONVERT(VARCHAR(5), ROW_NUMBER() OVER (ORDER BY column_id))
  + ': ' + name
FROM sys.columns
WHERE [object_id] = OBJECT_ID(N'schemaAsset.SystemDefined')
AND name NOT IN ('Location' /* , ... other columns ... */)
ORDER BY column_id; -- because you never know how SQL Server might order

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server How can specific columns be excluded from results when selecting from INFORMATION_SCHEMA?

3条答案

相关问题

热门标签

最新问答