How to select specific column names in SQL Server
11,118
Solution 1
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
Solution 2
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')
Author by
Admin
Updated on June 04, 2022Comments
-
Admin almost 2 years
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
-
Admin about 11 yearsYes I forgot this one to use, because I thought it will not work because the first time i try it there are errors, maybe just syntax error, anyway thanks sir :)
-
Admin about 11 yearsAh yes sir I can use that too, sorry I'm a newbie, thanks sir :)