SQL Server query to get the list of columns in a table along with Number, Data types, NOT NULL, and its source

Question

I need to write a query on SQL server to get the list of columns in a particular table including the generate number, its associated data types (with length), if they are null (Yes, else No) and lastly its source including the database and table name.

For instance, looking at AdventureWorksDW from table [dbo].[DimProduct], I can see:

CREATE TABLE [dbo].[DimProduct]
(
    [ProductKey] [INT] IDENTITY(1,1) NOT NULL,
    [ProductAlternateKey] [NVARCHAR](25) NULL,
    [ProductSubcategoryKey] [INT] NULL,
    [WeightUnitMeasureCode] [NCHAR](3) NULL,
    [SizeUnitMeasureCode] [NCHAR](3) NULL,
    [EnglishProductName] [NVARCHAR](50) NOT NULL,
    ...

I want to create a select statement to provide me the following result:

Number  Name                    Type            Is Null     Source
---------------------------------------------------------------------------------------------
1       ProductKey              int             No          AdventureWorksDW.dbo.DimProduct
2       ProductAlternateKey     nvarchar(25)    Yes         AdventureWorksDW.dbo.DimProduct
3       ProductSubcategoryKey   int             Yes         AdventureWorksDW.dbo.DimProduct
…       …                       …               …           …

Answer 1

For a single database, it's pretty easy to generate the list for all tables:

SELECT 
  [Number] = f.column_ordinal, 
  Name = f.name, 
  [Type] = f.system_type_name, 
  [Is Null] = CASE f.is_nullable WHEN 1 THEN 'Yes' ELSE 'No' END,
  Source = QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
FROM sys.tables AS t 
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
CROSS APPLY sys.dm_exec_describe_first_result_set
(
  N'SELECT * FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
  N'', 
  0
) AS f
ORDER BY Source, [Number];

If you just need it for one table, add:

WHERE s.name = N'dbo'
  AND t.name = N'DimProduct'

...but then the Source output column is kind of useless, since you already know what database, schema, and table you've retrieved.

For an unknown number of databases with unknown names, it's a little more complex; you can start with sp_ineachdb (part 1, part 2).