Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I made this query, to list tables, its columns, data type and etc:

select t.name as 'Table',
       c.name 'Column',
       ty.name 'Data_Type',
       c.max_length as 'Max_Lenght',
       c.is_nullable as 'Null',
       c.is_identity as 'Identity'
from sys.tables t
    join sys.columns c on t.object_id=c.object_id
    join sys.types ty on c.system_type_id=ty.system_type_id
        order by 1 

I have 2 questions:

Ques.1:

How can I add a column 'Is primary key?' to it? I could not find a table that help me ( I took a look at sys.indexes, sys.foreign_keys, INFORMATION_SCHEMA.table_constraints ).

Ques.2:

In sys.columns, int values have the max_lenght as 4, and some other fields too. What is this 4? 4kb per data? datetime and decimal too.

Thanks.

share|improve this question
4  
Also primary keys are not always a single column. They are often composite, made from 2 or more columns. – ypercubeᵀᴹ Feb 4 at 17:42
up vote 10 down vote accepted

Query that adds "is_primary_key" field for each column

The sys.key_constraints and sys.index_columns catalog views will identify the PK columns. You can JOIN them together and then use that set as a derived table to LEFT JOIN to your main query which will allow for not filtering out columns that are not part of a PK.

Also, you want to use [user_type_id] instead of [system_type_id] to avoid a Cartesian product. Most of the time there won't be a difference between these two fields. But if you have User Defined Data Types (UDDTs), or use the sysname datatype (which is an alias to NVARCHAR(128)), then the [system_type_id] value will be repeated in the sys.types catalog view.

The following query lists all fields in all tables, adding a computed field to denote if the column is part of the PK or not. It handles composite PKs as well.

SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey]
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
ORDER BY tbl.[name], col.[name];

Info on max_length column

The max_length column in sys.columns is the maximum number of bytes that the column can take up per row. In the case of fixed-length fields such as INT, DATETIME, etc, those fields always take up their maximum amount of space, unless you are using the SPARSE option (per column setting) or have enabled Data Compression (per index setting).

For variable-length fields such as VARCHAR, NVARCHAR, XML, etc, that value is the maximum number of bytes it can take up. A value of -1 indicates a value of approx 2 GB (Int32.MaxValue) which is used by the MAX (VARCHAR, NVARCHAR, and VARBINARY) and XML types.

The Unicode string types (NCHAR and NVARCHAR) that are not declared as MAX will display a max_length of 2 * declared_max since the (safe) assumption is that they use 2 bytes per "character". This is not always the case since Supplementary Characters are actually 4 bytes per "character". But, this is why sysname, being an alias for NVARCHAR(128), has a max_length of 256. Similarly, the less frequently used Double Byte Character Set (DBCS) collations that allow for mapping more than 256 characters in an 8-bit VARCHAR / CHAR field will store characters in either 1 or 2 bytes, depending on the character. Meaning, for VARCHAR data using a DBCS collation, and for NVARCHAR data, declaring a column or variable as VARCHAR(x) or NVARCHAR(x) does not guarantee x characters are storable: you can only fit x characters if all of those characters are of the standard length for that type (i.e. 1 byte for VARCHAR, 2 bytes for NVARCHAR). So, an NVARCHAR(3) field is given 6 bytes maximum to use. That can fit: 3 regular two-bytes Unicode characters, or 1 regular two-byte character and 1 Supplementary Character at 4 bytes (which is only 2 characters, not 3). It cannot fit 2 Supplementary Characters as that would require 8 bytes.

The deprecated TEXT, NTEXT, and IMAGE types that nobody is using anymore (that's sarcasm) show a max_length of 16, presumably being the size of the pointer left on the data page that points to the LOB page holding the data.

share|improve this answer
3  
Wow. this is what I need. thank you very much. It was more complicated than I tought. and it list the same number of rows as my query. Thanks – Rafael Piccinelli Feb 4 at 17:53
2  
epic. Thank you. I love when I ask something simple, and I end up learning a lot more hahaha. – Rafael Piccinelli Feb 4 at 18:51

Ques.1: First remember that primary keys are not always clustered keys which is what I think you really want. However the answer is pretty much the same either way. You need to look at sys.indexes to see the is_primary column. This will tell you that the index is they primary key. Then you need to look at sys.index_columns to see the actual columns in the index.

SELECT object_name(c.object_id) as Table_Name,
    c.name AS Column_Name,
    ty.name AS Data_Type,
    c.max_length AS Max_Length,
    c.is_nullable AS Nullability,
    c.is_identity AS Is_Identity,
    CASE WHEN PrimaryCols.column_id IS NOT NULL THEN 1 ELSE 0 END AS Is_Primary_Key
FROM sys.columns c
LEFT OUTER JOIN (
    SELECT ic.object_id, ic.column_id
    FROM sys.indexes ix
    JOIN sys.index_columns ic
        ON ix.object_id = ic.object_id
        AND ix.index_id = ic.index_id 
    WHERE is_primary_key = 1) PrimaryCols
    ON PrimaryCols.object_id = c.object_id
    AND PrimaryCols.column_id = c.column_id
JOIN sys.types ty
    ON c.system_type_id = ty.system_type_id

Ques.2 That number is the precision. Or the maximum number of bytes taken up by the field (this column for one row). In most cases maximum=actual but in a few (varchar, nvarchar) the actual is number of characters + 2 and maximum is what you put + 2. In the case of nchar & nvarchar each character actually takes up two bytes.

share|improve this answer
    
Hey @Kenneth thanks for your answer. I was trying to do something like this, but when I use your query, not all fields os some tables is listed. I will try to fix this andI will accept your answer If I can. – Rafael Piccinelli Feb 4 at 17:49
    
Sorry, just realized it's only pulling indexed columns (and might be pulling them twice) let me fix it real fast. – Kenneth Fisher Feb 4 at 18:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.