SQL Server - Can You Add Field Descriptions in Create Table

SQL Server - Can you add field descriptions in CREATE TABLE?

While you can't do it in CREATE TABLE, you can do it at the same time, in the same database script, using this approach:

CREATE table T1 (id int , name char (20))

EXEC   sp_addextendedproperty 'MS_Description', 'Employee ID', 'user', dbo, 'table', 'T1', 'column', id

EXEC   sp_addextendedproperty 'MS_Description', 'Employee Name', 'user', dbo, 'table', 'T1', 'column', name

Then you can see your entries using this:

SELECT   *
FROM   ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'T1', 'column', default)

Is it possible to add description for column while creating table in MSSQL QUERY

It's clunky in SQL Server. For some reason, they've never adopted the COMMENT syntax, and you can't add the comments directly in the CREATE TABLE statement.

After your CREATE statement, run the system stored procedure sp-addextendedproperty

There's an extended conversation on the topic under this question: SQL Comments on Create Table on SQL Server 2008

adding a column description

I'd say you will probably want to do it using the sp_addextendedproperty stored proc.

Microsoft has some good documentation on it.

Try this:

EXEC sp_addextendedproperty 
    @name = N'MS_Description', @value = 'Hey, here is my description!',
    @level0type = N'Schema',   @level0name = 'yourschema',
    @level1type = N'Table',    @level1name = 'YourTable',
    @level2type = N'Column',   @level2name = 'yourColumn';
GO

How to add column description in CREATE TABLE DDL

According to the documentation this should work:

CREATE TABLE mydataset.newtable (
  x STRING(10)     OPTIONS(description="denotes value of x"),
  y STRING(10)     OPTIONS(description="denotes value of y"),
  z BIGNUMERIC(35) OPTIONS(description="denotes value of z")
)

Is it possible to add a description/comment to a table in Microsoft SQL 2000+

Use extended properties. For example to add an extended property to a table in the dbo schema you can use:

EXEC sys.sp_addextendedproperty @name=N'<NameOfProp>', 
@value=N'<Value>' , @level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'<Table>'

You can update them:

EXEC sys.sp_updateextendedproperty 
   @name=N'MS_Description', @value=N'My Description' ,
     @level0type=N'SCHEMA',@level0name=N'dbo', 
      @level1type=N'TABLE'
     ,@level1name=N'<YOUR TABLE NAME>'

You can read them like:

SELECT *
FROM fn_listextendedproperty (NULL, 'schema','dbo', 'table', '<yourtable>', default, default);

or

SELECT
p.name AS [Name],p.value
FROM
sys.tables AS tbl
INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
WHERE
(tbl.name=N'<yourtablename>' and SCHEMA_NAME(tbl.schema_id)=N'dbo')
ORDER BY
[Name] ASC

add descriptions to fields in BigQuery using SQL

just want to post the solution in case someone has the same problem. dbt didn't update the descriptions in BQ itself. However, they released this new feature last month: https://github.com/fishtown-analytics/dbt/releases/tag/v0.17.0

Docs can be generated as usual and BQ will show the descriptions of tables and columns. You will only need to add the below to your dbt_project.yml file:

+persist_docs:
  relation: true
  columns: true

SQL comments on create table on SQL Server 2008

You can put comments on both tables and columns by creating what are called Extended Properties. You can put extended properties at both the table level and column level. This can be done via T-SQL or SSMS.

For example, in T-SQL it looks something like this:

sp_addextendedproperty 'BackColor', 'Red', 'user', '<schema name>', 'table', '<table name', 'column', '<column name>'.

You can read more about it on sp_addextendedproperty (Transact-SQL).

How to add a comment to an existing table column in SQL Server?

While creating a new table in SQL Server Management Studio, see this screenshot for adding a description to a column:

To do it programmatically:

EXEC sp_updateextendedproperty 
@name = N'MS_Description', @value = 'Your description',
@level0type = N'Schema', @level0name = dbo, 
@level1type = N'Table',  @level1name = Your Table Name, 
@level2type = N'Column', @level2name = Yuur Column Name;

---

Related Topics