Birth of the Singularity
I'm sure I'm not the first person to experience this and ask the question: Is ChatGPT is the beginning of the end of human race? Is this the event that portends humankind's extinction? The birth of the Singularity?
I had a use case today. I just wanted Microsoft Synapse Database Templates installed on a SQL Server database (Azure or otherwise). Unfortunately, the database templates can only be installed to a Data Lake not to a SQL Server directly.
However, I was able to query the structure using INFORMATION_SCHEMA.COLUMNS. A thought: Simply write a cursor to iterate over and create me the DDL statements needed. Didn't want to bother writing it myself. After googling ad nauseum, I asked ChatGPT to write it for me.
ATTEMPT #1 - 12/15/2022 10:00 AM
write a SQL Server cursor that iterates over INFORMATION_SCHEMA.COLUMNS and write CREATE TABLE statements
Here is an example of a SQL Server cursor that iterates over the INFORMATION_SCHEMA.COLUMNS table and generates CREATE TABLE statements:
DECLARE @column_name AS VARCHAR(MAX)
DECLARE @data_type AS VARCHAR(MAX);
DECLARE @table_name AS VARCHAR(MAX);
-- Create the cursor that will iterate over the columns in the INFORMATION_SCHEMA.COLUMNS table
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS;
-- Open the cursor
OPEN column_cursor;
-- Loop through each row in the cursor
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print the CREATE TABLE statement for the current table
PRINT 'CREATE TABLE ' + @table_name + ' (' + @column_name + ' ' + @data_type + ')';
-- Move to the next row in the cursor
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @table_name;
END;
-- Close and deallocate the cursor
CLOSE column_cursor;
DEALLOCATE column_cursor;;
This cursor will generate a CREATE TABLE statement for each table in the database, using the column names and data types from the INFORMATION_SCHEMA.COLUMNS table. Note that this is just an example, and the generated CREATE TABLE statements will not necessarily be complete or correct for all databases.
Rookie mistake! The code would write as many rows for a single table!
add handling for CHARACTER_MAXIMUM LENGTH for binary data, character data or text and image data
Here is an updated version of the cursor that includes handling for the CHARACTER_MAXIMUM_LENGTH column in the INFORMATION_SCHEMA.COLUMNS table:
DECLARE @column_name AS VARCHAR(MAX)
DECLARE @data_type AS VARCHAR(MAX);
DECLAR @character_maximum_length AS INT;
DECLARE @table_name AS VARCHAR(MAX);
-- Create the cursor that will iterate over the columns in the INFORMATION_SCHEMA.COLUMNS table
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS;
-- Open the cursor
OPEN column_cursor;
-- Loop through each row in the cursor
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @character_maximum_length, @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print the CREATE TABLE statement for the current table
PRINT 'CREATE TABLE ' + @table_name + ' (' + @column_name + ' ' + @data_type + '(' + @character_maximum_length + '))';
-- Move to the next row in the cursor
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @character_maximum_length, @table_name;
END;
-- Close and deallocate the cursor
CLOSE column_cursor;
DEALLOCATE column_cursor;;
In this updated version of the cursor, we have added a @character_maximum_length variable that will hold the value of the CHARACTER_MAXIMUM_LENGTH column for each row in the cursor. We have also updated the PRINT statement to include this variable in the CREATE TABLE statement. This will ensure that the generated CREATE TABLE statements include the correct length for binary, character, and text data types.
add a condition to check if @character_maximum_length is less than or equal to 0 or null and skip adding maximum length to those columns
Here is an updated version of the cursor that includes a condition to check if the @character_maximum_length variable is less than or equal to 0 or NULL, and skips adding the maximum length to the generated CREATE TABLE statement in these cases:
Recommended by LinkedIn
DECLARE @column_name AS VARCHAR(MAX)
DECLARE @data_type AS VARCHAR(MAX);
DECLAR @character_maximum_length AS INT;
DECLARE @table_name AS VARCHAR(MAX);
-- Create the cursor that will iterate over the columns in the INFORMATION_SCHEMA.COLUMNS table
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS;
-- Open the cursor
OPEN column_cursor;
-- Loop through each row in the cursor
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @character_maximum_length, @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print the CREATE TABLE statement for the current table
IF @character_maximum_length <= 0 OR @character_maximum_length IS NULL
BEGIN
PRINT 'CREATE TABLE ' + @table_name + ' (' + @column_name + ' ' + @data_type + ')';
END
ELSE
BEGIN
PRINT 'CREATE TABLE ' + @table_name + ' (' + @column_name + ' ' + @data_type + '(' + @character_maximum_length + '))';
END;
-- Move to the next row in the cursor
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @character_maximum_length, @table_name;
END;
-- Close and deallocate the cursor
CLOSE column_cursor;
DEALLOCATE column_cursor;;
modify the cursor to iterate in a nested fashion. Outer loop iterating once for each table while inner loop iterating once for as many columns as there are.
Here is an updated version of the cursor that uses a nested loop structure to iterate over the tables and columns in the database:
DECLARE @column_name AS VARCHAR(MAX)
DECLARE @data_type AS VARCHAR(MAX);
DECLAR @character_maximum_length AS INT;
DECLARE @table_name AS VARCHAR(MAX);
-- Create the cursor that will iterate over the tables in the database
DECLARE table_cursor CURSOR FOR
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS;
-- Open the table cursor
OPEN table_cursor;
-- Loop through each table in the database
FETCH NEXT FROM table_cursor INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print the CREATE TABLE statement for the current table
PRINT 'CREATE TABLE ' + @table_name + ' (';
-- Create the column cursor that will iterate over the columns in the current table
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name;
-- Open the column cursor
OPEN column_cursor;
-- Loop through each column in the current table
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @character_maximum_length;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print the column definition for the current column
IF @character_maximum_length <= 0 OR @character_maximum_length IS NULL
BEGIN
PRINT ' ' + @column_name + ' ' + @data_type + ',';
END
ELSE
BEGIN
PRINT ' ' + @column_name + ' ' + @data_type + '(' + @character_maximum_length + '),';
END;
-- Move to the next column in the cursor
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @character_maximum_length;
END;
-- Close and deallocate the column cursor
CLOSE column_cursor;
DEALLOCATE column_cursor;
-- Print the closing parenthesis for the current table
PRINT ');';
-- Move to the next table in the cursor
FETCH NEXT FROM table_cursor INTO @table_name;
END;
-- Close and deallocate the table cursor
CLOSE table_cursor;
DEALLOCATE table_cursor;
In this updated version of the cursor, we have added an outer loop that iterates over the distinct tables in the database. For each table, we create a new cursor that iterates over the columns in that table. The inner loop then generates the column definitions for each column in the current table, and the CREATE TABLE statement is printed for each table.
Note that this cursor still does not handle IMAGE data types, since the CHARACTER_MAXIMUM_LENGTH column does not apply to these data types. You would need to add additional logic to handle
please modify to handle IMAGE data types
Here is an updated version of the cursor that includes handling for IMAGE data types:
DECLARE @column_name AS VARCHAR(MAX)
DECLARE @data_type AS VARCHAR(MAX);
DECLAR @character_maximum_length AS INT;
DECLARE @table_name AS VARCHAR(MAX);
-- Create the cursor that will iterate over the tables in the database
DECLARE table_cursor CURSOR FOR
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS;
-- Open the table cursor
OPEN table_cursor;
-- Loop through each table in the database
FETCH NEXT FROM table_cursor INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print the CREATE TABLE statement for the current table
PRINT 'CREATE TABLE ' + @table_name + ' (';
-- Create the column cursor that will iterate over the columns in the current table
DECLARE column_cursor CURSOR FOR
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name;
-- Open the column cursor
OPEN column_cursor;
-- Loop through each column in the current table
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @character_maximum_length;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print the column definition for the current column
IF @data_type = 'IMAGE'
BEGIN
PRINT ' ' + @column_name + ' ' + @data_type + ',';
END
ELSE
BEGIN
IF @character_maximum_length <= 0 OR @character_maximum_length IS NULL
BEGIN
PRINT ' ' + @column_name + ' ' + @data_type + ',';
END
ELSE
BEGIN
PRINT ' ' + @column_name + ' ' + @data_type + '(' + @character_maximum_length + '),';
END;
END;
-- Move to the next column in the cursor
FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @character_maximum_length;
END;
-- Close and deallocate the column cursor
CLOSE column_cursor;
DEALLOCATE column_cursor;
-- Print the closing parenthesis for the current table
PRINT ');';
-- Move to the next table in the cursor
FETCH NEXT FROM table_cursor INTO @table_name;
END;
-- Close and deallocate the table cursor
CLOSE table_cursor;
DEALLOCATE table_cursor;
;
In this updated version of the cursor, we have added a check for the IMAGE data type in the inner loop. If the current column has an IMAGE data type, the column definition is printed without the maximum length, since this column does not have a maximum length.
This updated cursor should now generate correct CREATE TABLE statements for...
ATTEMPT #2: 12/15/2022 8:00 PM ET
Same prompt, few hours later:
write a SQL Server cursor that iterates over INFORMATION_SCHEMA.COLUMNS and write CREATE TABLE statements
DECLARE @table_name AS NVARCHAR(128)
@column_name AS NVARCHAR(128),
@data_type AS NVARCHAR(128),
@is_nullable AS BIT,
@column_default AS NVARCHAR(128),
@SQL AS NVARCHAR(MAX)
-- Create a cursor to iterate over the columns of all tables in the database
DECLARE column_cursor CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
-- Open the cursor
OPEN column_cursor
-- Loop through each row in the cursor
FETCH NEXT FROM column_cursor INTO @table_name, @column_name, @data_type, @is_nullable, @column_default
WHILE @@FETCH_STATUS = 0
BEGIN
-- If this is the first column in the table, start building a CREATE TABLE statement
IF @column_name = (SELECT MIN(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name)
BEGIN
SET @SQL = 'CREATE TABLE ' + @table_name + ' ('
END
-- Add this column to the CREATE TABLE statement
SET @SQL = @SQL + @column_name + ' ' + @data_type + ' ' +
CASE WHEN @is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ' ' +
CASE WHEN @column_default IS NOT NULL THEN 'DEFAULT ' + @column_default ELSE '' END + ', '
-- If this is the last column in the table, complete the CREATE TABLE statement and execute it
IF @column_name = (SELECT MAX(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name)
BEGIN
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ')'
EXEC sp_executesql @SQL
END
-- Move to the next row in the cursor
FETCH NEXT FROM column_cursor INTO @table_name, @column_name, @data_type, @is_nullable, @column_default
END
-- Close and deallocate the cursor
CLOSE column_cursor
DEALLOCATE column_cursor,
Bam! Right out of the gate, it's more complex. It now respects the table boundary... first and the last columns. Wow!
I like to think it has evolved.