Easy way to identify required fields in a table

Question

Scenario: Table with over 100 fields (not my doing... I inherited this) Only 50 these fields are required to be displayed on a web site They want to maintain the other 50 fields for historical purposes. There is a possibility that some of the not required fields may become required sometime in the future.

Problem: I'm looking for a way to easily indentify the 50 required fields such that I could pull the field names with a query.

Psuedo Query: Select FieldNames from TableName where Required = Yes


Is there a setting I could change?
What about using Extended Properties?

Thanks in advance for any direction you can provide.

Answer 1

Unless I'm missing a nuance to your question, use the INFORMATION_SCHEMA table for COLUMNS. This query identifies all the columns in table dbo.dummy that are required.

SELECT
    IC.COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS IC
WHERE
    IC.TABLE_SCHEMA = 'dbo'
    AND IC.TABLE_NAME = 'dummy'
    AND IC.IS_NULLABLE = 'NO'

After doing more thinking, perhaps you wanted a generic query that would grab all the required columns and then build out the select query. This query covers that possible request

DECLARE
    @hax varchar(max)
,   @schemaName sysname
,   @tableName sysname

SELECT
    @schemaName = 'dbo'
,   @tableName = 'dummy'

; WITH A AS
(
    -- this query identifies all the columns that are not nullable
    SELECT
        IC.TABLE_SCHEMA + '.' + IC.TABLE_NAME AS tname
    ,    IC.COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.COLUMNS IC
    WHERE
        IC.TABLE_SCHEMA = @schemaName
        AND IC.TABLE_NAME = @tableName
        AND IC.IS_NULLABLE = 'NO'
)
, COLUMN_SELECT (column_list) AS
(
    -- this query concatenates all the column names
    -- returned by the above
    SELECT STUFF((SELECT '], [' + A.Column_Name 
    FROM A 
    FOR XML PATH('')),1, 2, '') 
)
-- Use the above to build a query string
SELECT DISTINCT
    @hax = 'SELECT ' + CS.column_list + '] FROM ' + A.tname 
FROM
    A
    CROSS APPLY 
        COLUMN_SELECT CS

-- invoke the query
EXECUTE (@hax)

Answer 2

How about creating a view that only has the required fields.