Create tables from metadatatable

Question

Create tables from metadatatable

latitiacasta

SSCommitted

Points: 1982
More actions
July 8, 2019 at 7:25 am

#3660689

Hi ,
I would like to automate some process for that I am creating one T-SQL script which can read data from my metadata table and create all tables with appropriate datatypes etc. Please find attached script to create metadata table. I wrote below script but need to do more joining to get all columns name with comma separated.

DECLARE @Sql_Command NVARCHAR(MAX) = '';
SELECT @Sql_Command = @Sql_Command + '
IF EXISTS (SELECT * FROM sys.tables INNER JOIN sys.schemas ON schemas.schema_id = tables.schema_id WHERE tables.name = ''' + metatable.Table_Name + ''' AND schemas.name = ''' + Metatable.Schema_Name + ''')
BEGIN
DROP TABLE [' + Metatable.Schema_Name + '].[' + Metatable.Table_Name + '];
END
CREATE TABLE [' + Metatable.Schema_Name + '].[' + Metatable.Table_Name + ']
( [' + Metatable.Table_Name + '_Key] INT NOT NULL CONSTRAINT [PK_' + Metatable.Table_Name + '] PRIMARY KEY CLUSTERED,
[' + Metatable.Column_Name + '] ' + Metatable.Column_Datatype + ' ); '
FROM dbo.Metatable
select @Sql_Command

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

latitiacasta SSCommitted Points: 1982 More actions · Answer 1

please find below script for creating metatable

CREATE TABLE [dbo].[metatable](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Table_Name] [varchar](50) NOT NULL,
[Schema_Name] [varchar](128) NOT NULL,
[Column_Name] [varchar](50) NOT NULL,
[Column_Datatype] [varchar](50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[metatable] ON 
GO
INSERT [dbo].[metatable] ([ID], [Table_Name], [Schema_Name], [Column_Name], [Column_Datatype]) VALUES (1, N'Dim_Department_GroupName', N'dbo', N'GroupName', N'varchar(10)')
GO
INSERT [dbo].[metatable] ([ID], [Table_Name], [Schema_Name], [Column_Name], [Column_Datatype]) VALUES (2, N'Dim_JobTitle', N'dbo', N'JobTitle', N'nvarchar(20)')
GO
INSERT [dbo].[metatable] ([ID], [Table_Name], [Schema_Name], [Column_Name], [Column_Datatype]) VALUES (3, N'Dim_JobTitle', N'dbo', N'Jobcode', N'int')
GO
INSERT [dbo].[metatable] ([ID], [Table_Name], [Schema_Name], [Column_Name], [Column_Datatype]) VALUES (4, N'Dim_JobTitle', N'dbo', N'Jobdesc', N'nvarchar(200)')
Go
SET IDENTITY_INSERT [dbo].[metatable] OFF
GO

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I guess I just don't understand why anyone would do such a thing.

For deployments, it's easier to go with a script.

For documentation, it's easier to go with what SQL Server creates in the object tables.

For other things, it's easier to go with what SQL Server creates in object tables.

And, storing such meta-data like you're trying to do also requires that your stuff be updated to be in sync with what actually exists not to mention that you're not actually doing it as well as what the system does inherently.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

latitiacasta SSCommitted Points: 1982 More actions · Answer 3

I am trying to automate whole things from source to DW. I had already done from staging to DW I need to run one script which will handle all scenarios and load all dimensions tables and then facts. Now I am trying to automate from source to staging. I know it's not a good idea because the source will be different each time. But my idea behind it, first I will create extract layer which will be a copy of source data then once data in SQL server, it should be automated the whole process.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

The system itself already has all the metadata views you need. It's best to just use those views: sys.objects, sys.columns, sys.key_constraints, etc..

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

latitiacasta SSCommitted Points: 1982 More actions · Answer 5

Thanks, Jeff and Scott for your time.

Actually, once data is available in DB then only I will able to use system views, as you mentioned but in my case, I am trying to automate the process from Requirement gathering document I will able to create and populate DW. I had done that now. Sorry for the late reply.

Really appreciate your valuable time thank you very much