Skip to content

Instantly share code, notes, and snippets.

@shawty
Last active October 28, 2020 13:07
Show Gist options
  • Save shawty/82d2fc72517ee762e5c81ea8d57db4ce to your computer and use it in GitHub Desktop.
Save shawty/82d2fc72517ee762e5c81ea8d57db4ce to your computer and use it in GitHub Desktop.
SQL Server 2012 upwards, stored procedure to write a C# class for a given table name
create procedure [utils].[SqlTableToCSharp]
@tableName nvarchar(MAX)
as
begin
declare @classBody nvarchar(MAX) = '';
with tableColumns as (
select schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col.column_id,
col.name as column_name,
t.name as data_type,
col.max_length,
col.precision
from
sys.tables as tab
inner join
sys.columns as col
on tab.object_id = col.object_id
left join
sys.types as t
on col.user_type_id = t.user_type_id
),
propertyStrings as (
select
concat(
' public ',
case data_type
when 'int' then 'int'
when 'varchar' then 'string'
when 'nvarchar' then 'string'
when 'char' then
case max_length
when 1 then 'char'
else 'string'
end
when 'numeric' then 'decimal'
when 'float' then 'float'
when 'datetime' then 'DateTime'
when 'bit' then 'bool'
when 'smallint' then 'short'
else 'FIXMEDATATYPE'
end,
' ',
column_name,
' { get; set; }'
) as propertyLine
from
tableColumns
where
table_name = @tableName
)
select
@classBody = coalesce(@classBody + CHAR(13) + propertyLine, propertyLine)
from propertyStrings;
select
concat(
'public class ',
@tableName,
CHAR(13),
'{',
@classBody,
CHAR(13),
'}'
);
end
@shawty
Copy link
Author

shawty commented Oct 27, 2020

To use in SMSS, switch your output from "grids" to "text" (ctrl+t) in the SQL editor then execute in the following manner

EXEC @return_value = [utils].[SqlTableToCSharp]
@tableName = N''
GO

NOTE: You may also have to go into
TOOLS->OPTIONS->QUERY RESULTS->SQL SERVER->RESULTS TO TEXT

and change the maximum number of chars displayed in a column to a higher value, as SMSS will truncate a text output at 256 chars by default (Thanks MS!!)

@shawty
Copy link
Author

shawty commented Oct 28, 2020

28th Oct 2020 - Updated data types to include short/bool and better detection for char(1) vs string

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment