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 = '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

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 will be added or updated for the entire table.
        @Description (varchar(1000)): The description to add or update. If null, the existing description will be dropped.
    Returns: None
*/
CREATE 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

    -- check if the extended property exists
    if exists (
        select *
        from sys.extended_properties ep
            inner join sys.objects obj on obj.object_id = ep.major_id
            left join sys.columns col on col.object_id = ep.major_id and col.column_id = ep.minor_id
        where ep.name = 'Description' and obj.name = @TableName and coalesce(col.name,'') = coalesce(@ColumnName,'')
    )        
  	begin
        -- drop the description id the description is null
        if @Description is null
		begin
            if @ColumnName is null
            begin
                -- drop the table extended property
                exec sys.sp_dropextendedproperty @name = 'Description', @level0type = @level0type, @level0name = @level0name, @level1type = @level1type, @level1name = @level1name
            end
            else
            begin
                -- drop the column extended property
                exec sys.sp_dropextendedproperty @name = 'Description', @level0type = @level0type, @level0name = @level0name, @level1type = @level1type, @level1name = @level1name, @level2type = @level2type, @level2name = @level2name
            end
            return
        end

        if @ColumnName is null
        begin
            -- update the table extended property
            exec sys.sp_updateextendedproperty @name = 'Description', @value = @Description, @level0type = @level0type, @level0name = @level0name, @level1type = @level1type, @level1name = @level1name
        end
        else
        begin
            -- update the column extended property
            exec sys.sp_updateextendedproperty @name = 'Description', @value = @Description, @level0type = @level0type, @level0name = @level0name, @level1type = @level1type, @level1name = @level1name, @level2type = @level2type, @level2name = @level2name
        end
    end
	else
	begin
        -- skip the insertion if the description is null
        if @Description is null
			return

        if @ColumnName is null
        begin
            -- update the table extended property
            exec sys.sp_addextendedproperty @name = 'Description', @value = @Description, @level0type = @level0type, @level0name = @level0name, @level1type = @level1type, @level1name = @level1name
        end
        else
        begin
            -- update the column extended property
            exec sys.sp_addextendedproperty @name = 'Description', @value = @Description, @level0type = @level0type, @level0name = @level0name, @level1type = @level1type, @level1name = @level1name, @level2type = @level2type, @level2name = @level2name
        end
    end
END