How to get the column properties of a table?

Asked

Viewed 6,000 times

4

I am working with a table created by another person, and there is in it a field of CPF, would like to know what is the size of this field, how many characters it is able to support.

I know that in SQL-Server, in the query part, there is a command for this purpose, because I have already done, but I can’t find.

database sql-server
  • 1

    About "how many characters he is able to support", depends on how it was declared. You are using Management Studio to build the query?

  • 1

    I didn’t quite understand the part about "how many characters he is able to support", you refer to the amount of characters that the column will store sent in the value at the time of insert?

  • Yes, I’m using Management Studio.

  • I meant the size of the field itself, @Andréfilipe .

  • 1

    Ah, I understood Leonardo. In this case it depends on how much the attribute LENGTH return, the number of characters cannot exceed this value.

6 answers

6


  • 1

    By far the best alternative.

  • This had been the command I had seen before and is the best alternative in this case, I was looking for the keyboard shortcut for this, you know what would be?

  • 1

    The @Matheus, in another answer gave the shortcut: ALT + F1

  • 1

    When you said you had seen the command I figured it was the same one, how good it worked, I also did not know the shortcut kk

  • 2

    It would be nice for you to test the other options as well, return a feedback, and if it works give a vote in favor, people spent time writing a reply to help you

2

You can use the command

exec sp_help nomeTabela

or simply use the alt + F1 shortcut that is already SQL default in your table name.

  • 2

    To improve the content of your answer, you could explain to the questioner what this command exec ago.

  • Your answer added the shortcut I needed, but it was neither the most comprehensive nor the most direct, yet it helped me a lot!

  • Thanks for the tips !

1

"I would like to know what is the size of this field"

you can use the property COL_LENGTH which receives two mandatory parameters. The first is the table you want to check and the second is the column you want to know the value of the lenght defined in the execution DDL.

In your case, just carry out the following instruction: 

SELECT COL_LENGTH('nome_da_tabela', 'CPF') AS TAMANHO_CPF;

Source: Docs.microsoft.

  • 1

    The answer I found, meets more broadly and analyzes the table as a whole. But your answer is obviously the simplest and most direct answer to the question I asked!

  • Thanks @Leonardor.Oliveira, I’m happy to help you!

1

Use the table COLUMNS of information_schema:

SELECT column_name,
       data_type,
       character_maximum_length
  FROM information_schema.columns
 WHERE table_name = 'tabela'
   AND column_name = 'coluna';

COLUMNS

Returns a row for each column that can be accessed by the current user in the current database.

...

| Nome da coluna           | Tipo de dados   | Descrição

...

| DATA_TYPE                | nvarchar( 128 ) | Tipo de dados fornecido pelo sistema.

...

| CHARACTER_MAXIMUM_LENGTH | int             | Comprimento máximo, em caracteres, de dados binários, dados de caracteres e dados de texto e imagem.

  • For some reason, this query returned null in column size, did not solve for me, you would know the reason of having returned null?

  • 1

    @Oliveira what returned null? some column? or had no result, no row?

  • The column character_maximum_length returned null the rest is ok.

  • 1

    @Leonardor.Oliveira good question. What kind of column? VARCHAR?

  • The guy in that column is bigint

  • That’s why it doesn’t work?

  • @Oliveira yes, I thought it was VARCHAR (which would normally be correct). The BIGINT has a Rage of -2 63 (-9.223.372.036.854.775.808) to 2 63-1 (9.223.372.036.854.775.807)

  • Yes, @Sorack , but as I put the question, I don’t even know who created this table, and I’m still going to get a lot of stuff like this kkk !

Show 3 more comments

1

I found the answer in a post on Stack Overflow in English, follow the answer code: 

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 the table name you want to analyze.

Works from SQL-Server 2005 up!

The link to the original post is:

Answer - In English

1

Besides these forms mentioned above, there is another very simple way. Just select the table name in the SSMS Editor (SQL Server Management Studio) and press ALT + F1.

This will allow you to see the properties of the table in question, such as, what are the columns, data types, constraints, etc...

  • One of the comments above brought this same option, but anyway, it works.

Browser other questions tagged database sql-server

You are not signed in. Login or sign up in order to post.