SQL Comments on Create Table on SQL Server 2008

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 do you leave comments in SQL Server 2008 R2 view with SSMS?

Stop using the clunky and buggy view designer.

For a new view, just open a new query window and start typing. This will work fine:

USE MyDatabase;
GO

CREATE VIEW dbo.MyView
AS
  -- this view is cool
  SELECT whatever FROM dbo.wherever;

For an existing view, right-click the view and choose Script As > Alter instead. This will give you a much better experience (minus the ability to check and uncheck columns etc).

The various visual designers may look like they'll save you time (and the intentions were certainly good), but the implementation is terrible, there are all kinds of bugs and limitations, and they really haven't been improved or even touched in years.

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;

How to create a table from select query result in SQL Server 2008

Use following syntax to create new table from old table in SQL server 2008

Select * into new_table  from  old_table 

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

SQL Server 2008 Creating Table with date column with specified format

@MitchWheat is absolutely correct in his comment. Date/times are stored in an internal format.

One thing you can do is add a computed column to the table and use that for the correct format. You can do this as:

create table tbl_previous_policy (
    dtFrom datetime NOT NULL,
    dtTo datetime NOT NULL,
    service_time int NOT NULL,
    dtFrom_ddmmmyyyy as ( replace(convert(varchar(11), dtFrom, 106), ' ', '-') ),
    dtTo_ddmmmyyyy as ( replace(convert(varchar(11), dtTo, 106), ' ', '-') )
);

You can now use the additional fields when you want to output the date in your preferred format. You can also manipulate the existing values using the built-in date/time functionality.

Try to create a table from Select - SqL Server 2008 throws error

How about:

SELECT * into JmxMonSer FROM services WHERE monitoring_enabled=1

If the table already exists (and the columns types and ordering line up), you can use:

INSERT INTO JmxMonSer SELECT * FROM services WHERE monitoring_enabled=1

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

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).

---

Related Topics