Outputs of the SQL Server Scripts
Introduction
This is a supplement to the other SQL Server script pages. Here you will find information about the outputs of some of the scripts if you are not in a position to try them in SSMS right now.
Discovering the structure of Tables, Views, and Foreign Keys
ShowTableColumnDescriptions.sql will list all of the tables in your database including each column with its data type and Description. The output is a grid that you can copy and paste into a spreadsheet or drop into a Web page.
ShowTableColumnHavingRecords.sql will list the tables in your database including each column with its data type and Description, if the table has data. This script is handy when maintaining a large database bought from a vendor, where only a fraction of the tables are used in your application. This query may not run on remote computers.
Empty tables are filtered out using sys.dm_db_partition_stats.row_count.
This is what the output of ShowTableColumnDescriptions.sql and ShowTableColumnHavingRecords.sql looks like:
ShowViewColumnDescriptions.sql will list all of the views in your database including each column with its data type and Description.
This is what the output of ShowViewColumnDescriptions.sql looks like:
These view columns could use descriptions. We will look at scripts to help with that in a bit.
ShowTableFKDescriptions.sql will list details for each of the Foreign Keys in your database. It lists each foreign key constraint with its Schema and the related tables and columns.
This is what the output of ShowTableFKDescriptions.sql looks like:
Creating and maintaining descriptions of database objects in the database
Adding or updating descriptions of tables
AddTableDescriptions.sql will list all of the tables in your database that do not have object descriptions. The output is a set of SQL statements which allow you to add the description text and execute the statements to add the descriptions to the tables. This script works best if you set "Results To" to "Results to Text" using the Right-Click context menu.
The output of this query will have 'xxx' as a place holder for the new column description. Replace the "xxx" with a description of the column and run the script against the database.
The output of the AddTableDescriptions.sql script looks like this:
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value= N'xxx', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DayOfWeek'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value= N'xxx', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HolidayCategory'
GO
UpdateTableDescriptions.sql will list all of the tables in your database that do have object descriptions. The output is a set of SQL statements that allow you to Update the description text and execute the statements to update the descriptions of the tables. This script works best if you set "Results To" to "Results to Text" using the Right-Click context menu.
The output of the UpdateTableDescriptions.sql script looks like this:
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'The names of days of the week in numeric order.', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DayOfWeek'
GO
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'Category (group) this type of holiday falls into.', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HolidayCategory'
GO
Update (edit) any description that needs updating, and run the script against the database.
Adding or updating descriptions of the columns in tables
AddTableColumnDescriptions.sql will list all of the columns in your database tables that do not have object descriptions. The output is a set of SQL statements that allow you to add the description text and execute the statements to add the descriptions to the columns. This script works best if you set "Results To" to "Results To Text" using the Right-Click context menu.
The output of this query will have 'xxx' as a place holder for the new column description. Replace the "xxx" with a description of the column and run the script against the database.
UpdateTableColumnDescriptions.sql will list all of the columns in your database tables that do have object descriptions. The output is a set of SQL statements that allow you to update the description text and then execute the statements to update the descriptions. This script works best if you set "Results To" to "Results to Text" using the Right-Click context menu.
The output of UpdateTableColumnDescriptions looks like this:
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'Links to the login account.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Password', @level2type=N'COLUMN',@level2name=N'AccountID'
GO
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'Unique identifier for this account.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'AccountID'
GO
EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'Links to a role in the Account table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AccountAccountRole', @level2type=N'COLUMN',@level2name=N'AccountID'
GO
As you can see, the column statements are longer than the table statements because they add a column name to the end of the statement. Update (edit) any column description that needs updating, and run the script against the database.
This page is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License by Conrad Muller.
The scripts themselves are intended for education and are provided "AS IS"without warranty of any kind, either expressed or implied, including but not limited to implied warranties of merchantability and/or fitness for a particular purpose.