Here is my previous question on how to iterate a string in SQL Server:
下面是我之前关于如何在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
3 个解决方案
#1
3
How about using NOT IN
which you can use on the WHERE
clause to specify for another condition.
如何使用NOT,您可以在WHERE子句中指定另一个条件。
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
#2
1
Unless I am missing something, you can just use a WHERE
clause to exclude the column(s) you do not want:
除非我遗漏了什么,否则您可以使用WHERE子句来排除您不想要的列:
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')
如果有多个列,那么可以使用NOT IN ('Location', 'etc')
#3
0
Recommend sys.columns
instead of INFORMATION_SCHEMA
(here's why).
推荐系统。列而不是INFORMATION_SCHEMA(原因如下)。
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
#1
3
How about using NOT IN
which you can use on the WHERE
clause to specify for another condition.
如何使用NOT,您可以在WHERE子句中指定另一个条件。
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
#2
1
Unless I am missing something, you can just use a WHERE
clause to exclude the column(s) you do not want:
除非我遗漏了什么,否则您可以使用WHERE子句来排除您不想要的列:
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')
如果有多个列,那么可以使用NOT IN ('Location', 'etc')
#3
0
Recommend sys.columns
instead of INFORMATION_SCHEMA
(here's why).
推荐系统。列而不是INFORMATION_SCHEMA(原因如下)。
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