Easy way to identify required fields in a table

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.

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)

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

I am not sure if I understand the question correctly. Is this what you are looking for? The code is in MS SQL.

select  t.name as TABLE_NAME, c.name as COLUMN_NAME, c.is_nullable
from    sys.tables t
    inner join sys.columns c on c.object_id = t.object_id
WHERE   t.name = '<TableName>'
    and c.is_nullable = 0

There's no flag you can put on a field to determine whether it's relevant or not -- that's what the SELECT list is for. A couple of ideas...

1) Split the historical data out into a separate table, with a one-to-one relationship to the source table.

2) Re-name the historical fields in your table as "OBSOLETE_" + fieldname. This will at least give you a quick visual reference for when you're writing your sql.

3) Create a view. Big drawback to this one would be that you can take some big performance hits as soon as you try to use the view as a table in other queries. But if you're just pulling off it directly without joining it, you should be fine.

We use separate metatables describing all tables and columns in database. We store information like friendly name (for example 'username' column shoud be displayed to user as 'User name'), formating, etc. You could use this approach to store information about required columns.

We have tried object extended properties (sp_addextendedproperty etc.), but metatable(s) solution came up better for us.

Within TSQL this is not easy as you cannot dynamically build the columns in the select line nor the alias name for those columns. The parser and query optimizer need some stuff to be static. Is it an ASP.NET web site? In your development environment (e.g. C#) you could dynamically build the query.