Database Dictionary (1-2-1) --- Using Dynamic SQL Script

Note: this article is published on 10/20/2024.

This series of articles will discuss Database Dictionary related issues

Database Dictionary (1) --- Schema
- Database Dictionary (1-1) --- Server, Database, Schema, Table and Columns
- Database Dictionary (1-2) --- Create a System Data Dictionary Database
  - Database Dictionary (1-2-1) --- Using Dynamic SQL Script --- this article
Database Dictionary (2) --- Extended Properties
Database Dictionary (3) --- Loop Databases and Servers by AI

A - Introduction

Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed. Using dynamic SQL statements is adding more flexibility in coding, however it will add more burden on the programing and maintanence. Furthermore, it will introduce a sucerity concenr such like SQL Injection (see What Is SQL Injection).

In general, we would say, dynamic SQL Script is no good, due to, at least, two reasons:

Not readable, and not maintainable;
Security, possible the target of SQL injection

therefore, using dynamic SQL Script must be in the situation: 'have to'.

In our previous SDD database data input, Create a System Data Dictionary Database, we use dynamic SQL. Let us discuss the reasonabilities. The content of this article:

A - Introduction
B - Security is not a concern for using Dynamic SQL in a Stored Procedure
C - Requirement: Loop for Servers and Databases
- C.1 - Get Servers (including linnked servers and remote servers) by SQL
- C.2 - Get Databases by SQL
D - Looping Databases by Static SQL is not possible
- D.1 - All system database, such as sys.object, are all within one database, except servers and databases,
- D.2 - The USE key word cannot be used within a Stored Procedure
- D.3 - The database Identifier cannot be parameterized.
E - Dynamic SQL Script is the only choice for Looping databases

B - Security is not a concern for using Dynamic SQL in a Stored Procedure

First, due to used in Stored Procedure, within firewall, and especially, there is no parameter input, so the security issue is almost zero. Let us see if this is a 'have to' situation.

C - Requirement: Loop for Servers and Databases

We need to create a database including all information about the whole databases in a Server, or at least one instance. We need to dynamically loop the databases existing in this server or instance.This is possible by

C.1 - Get Servers (including linnked servers and remote servers) by SQL

SELECT * FROM sys.servers

Contains a row per linked or remote server registered, and a row for the local server that has server_id = 0. Such as

C.2 - Get Databases by SQL

SELECT * FROM sys.databases

Contains one row per database in the instance of SQL Server.

D - Looping Databases by Static SQL is not possible

Due to the following reasons:

All system database, such as sys.object, are all within one database, except servers and databases,
The USE key word cannot be used within a Stored Procedure
The database Identifier cannot be parameterized.

We show these one by one.

D.1 - All system database, such as sys.object, are all within one database, except servers and databases, see (left pane):

In this page: sys.sysobjects , the definitiion says:

This ontains one row for each object that is created within a database

D.2 - The USE key word cannot be used within a Stored Procedure:

a USE database statement is not alloweed in a procedure, function or trigger:

if we try to use a USE statement in a stored procedure, we will see the error like above.

D.3 - we cannot use the dynamic database name in the schema identifier, such as

E - Dynamic SQL Script is the only choice for Looping databases

The following code sample is from the previous article: Create a System Data Dictionary Database

References: