These are the files used in "Effortless SQL Query Crafting: Unleashing GitHub Copilot's Power" Medium post

#Effortless SQL Query Crafting - Unleashing GitHub Copilot's.txt

These are the files used in Effortless SQL Query Crafting: Unleashing GitHub Copilot's Power post. See here:

https://medium.com/@massimopiccardo/effortless-sql-query-crafting-unleashing-github-copilots-power-d09136e92de8

    DatabaseContextCreation_base.sql --> A basic query to generate a list of comments to use as a context for copilot generated queries
    DatabaseContextCreation_relations.sql --> An evolution of the base one, adding foreing keys information
    DatabaseContextCreation_descriptions.sql --> Another evolution, adding table and column free text descriptions
    sp_AddUpdateDescription.sql --> A stored procedure to simplify the definition of descriptions ofr tables and columns

DatabaseContextCreation_base.sql

-- CONTEXT GENERATION FOR COPILOT
-- Collects tables names, columns names
select
 '-- Table ' + t.table_schema + '.[' + t.table_name + '] (' + stuff(
  (
   select
    ', [' + c.column_name + '] ' + data_type + case
     when character_maximum_length is not null 
      then '(' + cast(character_maximum_length as varchar(10)) + ')'
     else ''
    end + case
     when is_nullable = 'NO' then ' not null'
     else ''
    end
   from
    information_schema.columns c
   where
    c.table_name = t.table_name
   order by
    ordinal_position for xml path('')
  ),1,2,''
 ) + ')' as TableDescription
from
 information_schema.tables t
where
 table_type = 'BASE TABLE'
order by
 t.table_schema,
 t.table_name 

DatabaseContextCreation_relations.sql

-- CONTEXT GENERATION FOR COPILOT
-- Collects tables names, columns names, foreign keys relations

with foreingKeysRelations as (
 -- get all foreign keys relations in current database
 select
  distinct t.name as sourceTableName,
  c.name as sourceColumnName,
  rt.name as targetTableName,
  fc.name as targetColumnName,
  ' /* references ' + rt.name + '.' + fc.name + ' */ ' as [value]
 from
  sys.columns c
  inner join sys.foreign_key_columns fkc on fkc.parent_object_id = c.object_id
  and fkc.parent_column_id = c.column_id
  inner join sys.columns fc on fc.object_id = fkc.referenced_object_id
  and fc.column_id = fkc.referenced_column_id
  inner join sys.tables t on t.object_id = fkc.parent_object_id
  inner join sys.tables rt on rt.object_id = fkc.referenced_object_id
 where
  c.object_id in (
   select
    object_id
   from
    sys.tables
   where
    type = 'U'
  )
)
select
 '-- Table ' + t.table_schema + '.[' + t.table_name + '] (' + stuff(
  (
   select
    ', [' + c.column_name + '] ' + data_type + case
     when character_maximum_length is not null 
      then '(' + cast(character_maximum_length as varchar(10)) + ')'
     else ''
    end + case
     when is_nullable = 'NO' then ' not null'
     else ''
    end
   from
    information_schema.columns c
   where
    c.table_name = t.table_name
   order by
    ordinal_position for xml path('')
  ),1,2,''
 ) + ')' as TableDescription
from
 information_schema.tables t
where
 table_type = 'BASE TABLE'
order by
 t.table_schema,
 t.table_name

DatabaseContextCreation_relations_descriptions.sql

-- CONTEXT GENERATION FOR COPILOT
-- Collects tables names, columns names, foreign keys relations 
-- and column descriptions as comments if available

with descriptions as (
	select
		' /*' + cast(ep.value as varchar(1000)) + '*/ ' as [value],
		obj.name as table_name,
		col.name as column_name,
		case
			when minor_id = 0 then 'TABLE'
			else 'COLUMN'
		end as description_type
	from
		sys.extended_properties ep
		inner join sys.objects obj on obj.object_id = ep.major_id
		/*decode minor_id as coulmn id*/
		left join sys.columns col on col.object_id = ep.major_id
		and col.column_id = ep.minor_id
	where
		ep.name = 'MS_Description'
),
foreignKeysRelations as (
	-- get all foreign keys relations in current database
	select
		distinct t.name as sourceTableName,
		c.name as sourceColumnName,
		rt.name as targetTableName,
		fc.name as targetColumnName,
		' /* references ' + rt.name + '.' + fc.name + ' */ ' as [value]
	from
		sys.columns c
		inner join sys.foreign_key_columns fkc on fkc.parent_object_id = c.object_id
		and fkc.parent_column_id = c.column_id
		inner join sys.columns fc on fc.object_id = fkc.referenced_object_id
		and fc.column_id = fkc.referenced_column_id
		inner join sys.tables t on t.object_id = fkc.parent_object_id
		inner join sys.tables rt on rt.object_id = fkc.referenced_object_id
	where
		c.object_id in (
			select
				object_id
			from
				sys.tables
			where
				type = 'U'
		)
)
select
	'-- Table ' + t.table_schema + '.[' + t.table_name + '] ' + coalesce(td.[value], '') + ' (' + stuff(
		(
			select
				', [' + c.column_name + '] ' + data_type + case
					when character_maximum_length is not null then '(' + cast(character_maximum_length as varchar(10)) + ')'
					else ''
				end + case
					when is_nullable = 'NO' then ' not null'
					else ''
				end + coalesce(cd.[value], '') + coalesce(fkr.[value], '')
			from
				information_schema.columns c
				left join descriptions cd on cd.table_name = c.table_name
				and cd.column_name = c.column_name
				and cd.description_type = 'COLUMN'
				left join foreignKeysRelations fkr on fkr.sourceTableName = c.table_name
				and fkr.sourceColumnName = c.column_name
			where
				c.table_name = t.table_name
			order by
				ordinal_position for xml path('')
		),1,2,''
	) + ');'
from
	information_schema.tables t
	left join descriptions td on td.table_name = t.table_name
	and td.description_type = 'TABLE'
where
	table_type = 'BASE TABLE'
order by
	t.table_schema,
	t.table_name

RecreateDescriptions.sql

DECLARE @script NVARCHAR(MAX) = N'';

SELECT @script = STRING_AGG(
    CAST(
        CASE 
            WHEN ep.minor_id = 0 THEN
                'exec sp_AddUpdateDescription @TableName = ''' 
                + o.name 
                + ''', @Description = ''' 
                + REPLACE(CAST(ep.value AS NVARCHAR(MAX)), '''', '''''') 
                + ''';'
            ELSE
                'exec sp_AddUpdateDescription @TableName = ''' 
                + o.name 
                + ''', @ColumnName = ''' 
                + c.name 
                + ''', @Description = ''' 
                + REPLACE(CAST(ep.value AS NVARCHAR(MAX)), '''', '''''') 
                + ''';'
        END 
    AS NVARCHAR(MAX))
    , CHAR(13) + CHAR(10)
) WITHIN GROUP (ORDER BY o.name, ep.minor_id)
FROM sys.extended_properties ep
    INNER JOIN sys.objects o ON ep.major_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.columns c 
        ON ep.major_id = c.object_id 
        AND ep.minor_id = c.column_id
WHERE ep.name = 'MS_Description'
    AND ep.class = 1
    AND o.type = 'U'
    AND s.name = 'dbo';

-- Return as clickable XML in SSMS (no truncation)
SELECT CAST('<![CDATA[' + @script + ']]>' AS XML) AS GeneratedScript;

sp_AddUpdateDescription.sql

/*
    Stored procedure: sp_AddUpdateDescription
    Description: Adds or updates a description for a table or column in a SQL Server database.
    Parameters:
        @SchemaName (varchar(50)): The name of the schema containing the table or column. Default is 'dbo'.
        @TableName (varchar(50)): The name of the table to add or update the description for.
        @ColumnName (varchar(50)): The name of the column to add or update the description for. 
                                   Default is null, which means the description applies to the entire table.
        @Description (varchar(1000)): The description to add or update. If null, the existing description will be dropped.
    Returns: None
*/
CREATE OR ALTER PROCEDURE [dbo].[sp_AddUpdateDescription]
    @SchemaName varchar(50) = 'dbo',
    @TableName varchar(50),
    @ColumnName varchar(50) = null,
    @Description varchar(1000)
AS
BEGIN
    SET NOCOUNT ON;

    declare @level0type varchar(50) = 'SCHEMA'
    declare @level1type varchar(50) = 'TABLE'
    declare @level2type varchar(50) = 'COLUMN'

    declare @level0name varchar(50) = @SchemaName
    declare @level1name varchar(50) = @TableName
    declare @level2name varchar(50) = @ColumnName

    -- Step 1: Attempt to drop any existing MS_Description property.
    -- If it doesn't exist, swallow the error and continue.
    begin try
        if @ColumnName is null
        begin
            -- drop the table-level extended property
            exec sys.sp_dropextendedproperty 
                 @name      = N'MS_Description',
                 @level0type = @level0type, @level0name = @level0name,
                 @level1type = @level1type, @level1name = @level1name;
        end
        else
        begin
            -- drop the column-level extended property
            exec sys.sp_dropextendedproperty 
                 @name      = N'MS_Description',
                 @level0type = @level0type, @level0name = @level0name,
                 @level1type = @level1type, @level1name = @level1name,
                 @level2type = @level2type, @level2name = @level2name;
        end
    end try
    begin catch
        -- Property didn't exist — safe to ignore.
        -- (Optional) You could check ERROR_NUMBER() = 15217 specifically if you want to 
        -- re-throw any other unexpected errors:
        --
        if ERROR_NUMBER() <> 15217  throw;
    end catch

    -- Step 2: If the caller passed a NULL description, they just wanted a drop — we're done.
    if @Description is null
        return;

    -- Step 3: Add the new extended property.
    if @ColumnName is null
    begin
        -- add the table-level extended property
        exec sys.sp_addextendedproperty 
             @name      = N'MS_Description',
             @value     = @Description,
             @level0type = @level0type, @level0name = @level0name,
             @level1type = @level1type, @level1name = @level1name;
    end
    else
    begin
        -- add the column-level extended property
        exec sys.sp_addextendedproperty 
             @name      = N'MS_Description',
             @value     = @Description,
             @level0type = @level0type, @level0name = @level0name,
             @level1type = @level1type, @level1name = @level1name,
             @level2type = @level2type, @level2name = @level2name;
    end
END
GO