SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints

by

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.

More Related Contents:

  1. Querying data by joining two tables in two database on different servers
  2. Generate a resultset of incrementing dates in TSQL
  3. How to check which locks are held on a table
  4. Get top 1 row of each group
  5. Referring to a Column Alias in a WHERE Clause
  6. How can I schedule a job to run a SQL query daily?
  7. Convert Month Number to Month Name Function in SQL
  8. Reset identity seed after deleting records in SQL Server
  9. How to avoid the “divide by zero” error in SQL?
  10. Real life example, when to use OUTER / CROSS APPLY in SQL
  11. Getting result of dynamic SQL into a variable for sql-server
  12. How to get difference between two rows for a column field?
  13. Better techniques for trimming leading zeros in SQL Server?
  14. What’s the best way to select the minimum value from several columns?
  15. Splitting delimited values in a SQL column into multiple rows
  16. What does =* mean?
  17. varbinary to string on SQL Server
  18. SQL : in clause in stored procedure:how to pass values
  19. SQL GROUP BY CASE statement with aggregate function
  20. separate comma separated values and store in table in sql server
  21. Favourite performance tuning tricks [closed]
  22. Best way to do nested case statement logic in SQL Server
  23. SQL Server 2005 ROW_NUMBER() without ORDER BY
  24. Can an INNER JOIN offer better performance than EXISTS
  25. Using SQL Server as a DB queue with multiple clients
  26. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. SQL Sort Order with Null values last
  29. Insert all values of a table into another table in SQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment