SQL Server – How to Select Column Names by Data Type

sql server

How would I get the names of all the columns in a table that are of a specific type such as datetime?

Even better — How can I do the same thing but with multiple tables joined together and then list the column name along with the table it comes from?

Best Answer

How would I get the names of all the columns in a table that are of a specific type such as datetime?

(output as schema_name | table_name | column_name | data_type):

SELECT Object_Schema_name(c.object_id) as [SCHEMA_NAME]
    ,object_NAME(c.object_id) AS TABLE_NAME
    ,c.NAME AS COLUMN_NAME
    ,t.NAME AS DATA_TYPE
    -- add / remove columns as per need
    --,c.max_length AS MAX_LENGTH
FROM sys.all_columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
where Object_Schema_name(c.object_id) <> 'sys' -- not sys schema
    -- filters 
        and t.name = 'Datetime'
    --  and object_NAME(c.object_id) = 'someTableName'

Related Solutions

Type Conversion Impact on Cardinality Estimates in SQL Server

The result of the query seems to be correct and the server executes it quickly.

But the root node of the execution plan has a warning:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(128),[o].[name],0)) may affect "CardinalityEstimate" in query plan choice

What does that mean in this context? Is such a complex filter confusing the optimizer? Is it something I should be worried about?

The warning is informational. If your query performed slowly, or you noticed that cardinality estimates were incorrect, the warning would give you information about where to look for a possible cause.

The warning is triggered by the implicit conversion used for the collation change. If using the collation is the easiest way to get correct results, feel free to leave it as it is. Alternatively, if you explain more about why it is needed, someone will advise you.

As an aside, the REPLACE could be replaced with:

REPLICATE(N'[0-9A-F]', 8);

(This answer is a summary of the comments to the question.)

SQL Server – Using Column Names in Separate Table

What you're looking for is Dynamic SQL Pivots. A PIVOT will turn row values into columns based on an aggregate, however you need to define the column names as part of the PIVOT, luckily we can do that on the fly with dynamic SQL. The following should generate the result set you want for a particular company (replace rowValues with your table name):

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'

SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()] 
                        FROM dbo.Headers H
                        WHERE H.company = @Company  
                        ORDER BY H.fieldNumber
                        FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT company,jobNumber,'+@Columns+'
FROM
(
    SELECT h.company,RV.jobNumber,RV.information,h.description
    FROM Headers h
            INNER JOIN dbo.RowValues RV
                ON RV.fieldNumber = h.fieldNumber
                   AND RV.company = h.company
    WHERE h.company = '+@Company+'  
) as Data
PIVOT
(
    MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY jobNumber ASC'

EXEC sp_executesql @sql;

Unfortunately due to the possibility that different companies will have different columns there is no reliable way to combine all the the possible sets the above query could generate. Depending on how you want to use this query the easy option is to loop through all the companies and call an SP that contains the above, allowing you to output each company as a separate SELECT. Or you can do something with your SSIS package to output each one into a file.

If you want to insert the data into a table matching the calculated schema you can do a SELECT INSERT and a bit more dynamic SQL to get the data where it needs to go.

Best Answer

Related Solutions

Type Conversion Impact on Cardinality Estimates in SQL Server

SQL Server – Using Column Names in Separate Table

Related Question