SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
Solution 1
To avoid duplicate rows for some columns, use user_type_id instead of system_type_id.
SELECT
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('YourTableName')
Just replace YourTableName
with your actual table name - works for SQL Server 2005 and up.
In case you are using schemas, replace YourTableName
by YourSchemaName.YourTableName
where YourSchemaName
is the actual schema name and YourTableName
is the actual table name.
Solution 2
The stored procedure sp_columns returns detailed table information.
exec sp_columns MyTable
Solution 3
You could use the query:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, DATETIME_PRECISION,
IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName'
to get all the metadata you require except for the Pk information.
Solution 4
In SQL 2012 you can use:
EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'
This will give you the column names along with their properties.
Solution 5
Try this:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = 'tablename' and COLUMN_NAME = 'columnname'
Related videos on Youtube
Shrayas
Updated on April 08, 2022Comments
-
Shrayas about 2 years
I need to write a query on SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much.
But now i also need to get, in the same table, against a column -
TRUE
if that column is a primary key.How do i do this?
My expected output is:
Column name | Data type | Length | isnull | Pk
-
DOK about 14 yearsCould you show the code you have already?
-
-
Shrayas about 14 yearsi did that :) But i need the PK too :|
-
Shrayas about 14 yearswell. It doesnt give me the required result :(
-
Andrew Savinykh almost 12 yearsThis gives wrong lengths for the nvarchar, etc type of columns. It gives the byte length that is twice as much as the length in the column type.
-
marc_s almost 12 yearsThose lengths are not wrong - it does give the byte length - that's the maximum possible length in bytes... if you want to calculate space etc., that's the length you want to get....
-
Nathan Jones over 11 yearsI'm getting an error when I try to run this: Server 'CLM', Line 5 Incorrect syntax near the keyword 'precision'.
-
Nathan Jones over 11 yearsI'm running it in SQL Server 2000. I changed all of the field names to their SQL Server 2000 counterparts, and it works now.
-
marc_s over 11 years@NathanJones: as I already said in the answer - this script is for SQL Server 2005 and newer only!
-
Nathan Jones over 11 yearsSorry about that! I didn't read that last part until after I started having trouble. Thanks for your help!
-
Artemix over 11 yearsHow does your answer differ from the one posted by Ajadex? Both answers do not return Primary Key information.
-
Ed Pecyna about 11 yearsYou may want to throw a DISTINCT clause in there also... I got some duplicate column names in my results.
-
Ed Pecyna about 11 yearsADDENDUM: Even with DISTINCT you can end up with 2 records for 1 column.
-
Our Man in Bananas about 11 years+1 ahh, but now I want to get the data in my table alongside the column datatypes... any thoughts
-
hobs over 10 yearsWorked well for me in Django using pyodbc and FreeTDS on fedora when I needed to augment the simple
manage.py inspectdb
command. Here's the code -
marc_s about 9 years@Terry: the catalog views return the max length in bytes (not in characters) - already been talked about, see 2nd and 3rd comments ...
-
Santhos about 9 yearsIt works on views as well (no surprise) and it also works on Azure SQL.
-
Doc Holiday over 8 yearsWorks great for me SQL Server 2012 :)
-
Leonardo Marques de Souza about 8 yearsmsdn.microsoft.com/pt-br/library/ms189813(v=sql.120).aspx sp_fkeys sp_pkeys
-
Leonardo Marques de Souza about 8 yearsexec sp_pkeys exec sp_fkeys
-
Rafa Barragan about 8 yearsHe ask for a query, but you're right this way allows you to see all the info.
-
Toxantron about 8 yearsPlease use indentation instead of inline Markup and add some explaination to your answer.
-
Ivan almost 8 yearsWHERE c.object_id = OBJECT_ID('YourTableName') .... I needed WHERE c.object_id = OBJECT_ID('MySchema.MyTableName') and then everything worked fine.
-
molerus over 7 yearsOk, with that awesome query i get the byte length for every column. I need to know how many characters fit in nvarchar columns, can i simply divide maxlength between 2 to find the answer? I have a nvarchar(10) column, maxlength returns 20, can i consider that a single character always needs 2 bytes to be stored in ddbb? What does the byte per character number depend on? Thanks
-
marc_s over 7 years@molerus: yes -
nvarchar
(andnchar
) are always 2 byte = 1 character -
Razvan Socol over 7 yearsThis query returns duplicated columns if you have multiple indexes involving the same column. To fix it, replace the last two joins with the following:
LEFT OUTER JOIN sys.index_columns ic LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id ON ic.object_id = c.object_id AND ic.column_id = c.column_id AND i.is_primary_key=1
-
Akshay Chawla almost 7 yearsthis query has saved lots of time for me, as my tables has 60+ columns. Also this worked perfectly.
-
Martijn Pieters over 6 yearsif this question has already been answered, flag the post as a duplicate.
-
reggaeguitar about 6 yearsWhy ORDER BY 2?
-
yeOldeDataSmythe over 5 yearsI found that I still received some duplicates with the left outer joins. Once I removed them and the Primary Key column, the duplicates disappeared. Wrapping this within a function would allow you to insert these rows into the return table and then update if the primary key is still needed. The table returns quickly.
-
dyslexicanaboko over 5 yearsI think it is a bit deceptive to say that max_length is your length when it absolutely isn't. Pretty much every query I have found makes this false assumption. I don't understand why there isn't a separate column for just giving me the length. Instead this is what I found that I have to use: docs.microsoft.com/en-us/sql/t-sql/functions/… which has the same problem apparently.
-
dyslexicanaboko over 5 yearsYou can say that's obvious but it isn't. It should be explicitly stated and already available to the user. Why should I have to do this obvious calculation? Just give me the same length I see in my editor and when I define my schema.
-
dyslexicanaboko over 5 yearsYou are making my point for me - IT DOES DO THAT. I am looking at my editor and I see 255, I query the column and it is returning 510. I don't want to see 510 I want to see 255 which is what I inputted. I don't want to be tasked with dividing it by 2 which is pretty annoying. That's not what I entered.
-
marc_s over 5 years@dyslexicanaboko: you're right - and I'm baffled - I was convinced it showed the length in characters - not bytes. Probably never realized since I always use
VARCHAR
and hardly ever Unicode ...... -
dyslexicanaboko over 5 yearsIt's all good man - you are very active on SO, so thank you for taking the time to respond to me.
-
George Menoutis over 5 yearsthe DDL Name is SO useful for dynamic-sql that creates tables! Thanks!!
-
Allen over 5 yearsyour condition of Constraint should be the other way around.
-
Abdullah Al Mamun over 5 yearsAnd how to know the foreign Keys in the table with the query
-
marc_s over 5 years@AbdullahAlMamun: check out the
sys.foreign_keys
catalog view -
Sylvain Gantois almost 5 yearsanswer should be updated to handle schemas (for instance 2 tables with same name in different schemas)
-
Mitch Wheat almost 4 years@marc_s : unfortuntaely, this has a flaw if the Primary key column(s) appear in other indexes: create table dbo.DummyTable ( id int not null identity(0,1) primary key, Msg varchar(80) null ); create index NC_DummyTable_id ON DummyTable(id);
-
Ram almost 4 yearsusing 'information_schema.columns' we can view info for all tables columns including viewing table name and schema.
-
Mitch Wheat almost 4 years
-
Uttam over 3 yearsThis works for Excel files opened with OPENROWSET as well while many other solutions don't. Thank you.
-
Daniel about 3 yearsIf using this, note that
MyTable
really is just the table name, not the schema. To filter to a schema, add it as a second parameter:exec sp_columns 'MyTable', 'MySchema'