AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
![]() "UK" when column is part of unique key.Ĭheck constraint flag. ![]() "PK" when column is part of table primary key.įoreign key flag. "Y" if column is nullable, "N" if column is not nullable. For instance, varchar(100) or decimal(8, 2). For instance, varchar or decimal.ĭata type with information about scale/precision or string length. On indexes.index_id = index_columns.index_idĪnd indexes.object_id = index_columns.object_id Group by fc.parent_column_id, fc.parent_object_id On index_columns.object_id = indexes.object_idĪnd index_columns.index_id = indexes.index_id When c_t.name in ('decimal', 'numeric') thenĬase when def.definition is not null then def.definitionĬase when pk.column_id is not null then 'PK'Ĭase when fk.parent_column_id is not null then 'FK'Ĭase when uk.column_id is not null then 'UK'Ĭase when ch.check_const is not null then ch.check_constĬc.definition as computed_column_definition, When t.name in ('decimal', 'numeric') thenĬast(col.precision as varchar(4)) + ', ' + When t.name in ('datetime2', 'datetimeoffset', Query select schema_name(tab.schema_id) as schema_name,Ĭase when t.name in ('binary', 'char', 'nchar', This query returns list of tables and their columns with details. ![]() Query select schema_name(v.schema_id) as schema_name, This query returns list of database views with their definition SQL and a comment. List of views with definition and comments Query select schema_name(tab.schema_id) as schema_name, This query returns list of tables in a database sorted by schema and table name with comments and number of rows in each table. List of tables with number of rows and comments You can also find 100+ other useful queries here.ġ. Select = + '' + p.name + '' + convert(varchar(100), x.value) + '' + convert(varchar(50), p.This is a list of handy SQL queries to the SQL Server data dictionary. Stored ProcedureDescriptionLast Modified Date' Select = + '' + i.name + '' + i.type_descĬonvert(varchar(3), (CASE i.is_primary_keyĪnd t.name = by t.name asc, Is_Primary_Key desc, i.name asc Select = + '' + convert(varchar(100), c.name) +'' + ISNULL(convert(varchar(200), x.value), 'No description available') + '' + convert(varchar(3), column_id) + '' + ty.name + '' + convert(varchar(4), c.max_length) + '' + convert(varchar(4), c.precision) + '' + PRINT 'Last Modified:- ' + Print HTML Table Columns Select = (s.name + '.' + t.name), = convert(varchar(1000), x.value), = modify_date SET = (SELECT DB_NAME() As 'Database Name') Ensure no additional lines are printed to the output You can do this within SQL Server Management Studio (right click the object, select Properties and then the Extended Properties tab), but I find it easier to use the built-in sys.sp_addextendedproperty procedure.ĮXEC Your Description Here' Table Name Here'ĮXEC Your Description Here' Table Name Column Name Here' We all hate creating documentation, don’t we?! The example includes tables, columns and stored procedures, but it would be fairly simple to tweak the query to include additional objects such as views, users, primary and foreign keys etc.īefore running the query, it is highly recommended to add descriptions to the tables, columns and procedures. It produces HTML output that can be uploaded to a web server, or you could copy and paste into Word. ![]() The T-SQL script at the bottom of the page produces a simple data dictionary for a SQL Server Database, along with descriptions of its objects. ![]()
0 Comments
Read More
Leave a Reply. |