Im kinda new to SQL so I'm sorry for a question that might sound trivial to some of you. Is there any "clever" way to copy paste/information of SQL tables ("design" option) into Excel? Example of table information

In the example I've selected one particular table; I can do my ordinary copy/paste of the three columns into Excel, and so on for other tables. The problem is that I have to do this for hundreds of tables. How can I automate this? Thank you!

CodePudding user response：

You could leverage some of the system tables to help build this information. But you need more than 3 columns of data. I will leave it to you to determine how you want to display all this information. This query will give you all the tables and columns along with their nullability and datatype.

select TableName = t.name 
    , ColumnName = c.name 
    , DatatypeName = st.name
    , MaxLength = case when st.name in ('nchar', 'nvarchar') then c.max_length / 2 else c.max_length end
    , c.precision
    , c.scale
    , c.is_nullable
from sys.columns c
join sys.tables t on t.object_id = c.object_id
join sys.types st on st.user_type_id = c.user_type_id
order by t.name
    , c.column_id