February 9, 2024 at 6:15 pm
Hi
I need some help on this request
Create a dynamic script to create view for all tables having MAX columns in the database
And if the column is MAX then I need to use CONVERT(VARCHAR(2000), SUBSTRING (COLUMN_NAME,1,2000)) as COLUMN_NAME in the view
Thanks
February 9, 2024 at 6:23 pm
And what would you want to name the view?
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".
February 9, 2024 at 7:50 pm
Here's a rough go at it. It seems to work in my environment, but any really wide table will be a problem for PRINT. I would not blindly run a script like this. Assuming the number of tables is manageable I would verify the select for each one before creating the view. This does not guarantee the columns will be ordered by ORDINAL_POSITION. I'm know if can be done, but I don't remember how. I think CHARACTER_MAXIMUM_LENGTH = -1 means MAX, but I would verify.
SET NOCOUNT ON
DECLARE @Total INT,
@Counter INT = 1,
@TableName VARCHAR(1000),
@TableSchema VARCHAR(20),
@SQLString1 VARCHAR(1000),
@SQLString2 VARCHAR(MAX),
@SQLString3 VARCHAR(MAX)
DROP TABLE IF EXISTS #Tables
CREATE TABLE #Tables (RecordID INT IDENTITY(1,1),
TableSchema VARCHAR(500),
TableName VARCHAR(50))
INSERT #Tables (TableSchema,TableName )
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'VARCHAR'
AND CHARACTER_MAXIMUM_LENGTH = -1
INTERSECT
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
SET @Total = @@ROWCOUNT
WHILE @Counter <= @Total
BEGIN
SELECT @TableName = TableName,
@TableSchema = TableSchema
FROM #Tables
WHERE RecordID = @Counter
SET @SQLString1 = CONCAT('CREATE VIEW ', @TableSchema, '.uv',@TableName, CHAR(13), 'AS', CHAR(13), 'SELECT ')
SELECT @SQLString2 = STRING_AGG(ColName,', ')
FROM (
SELECT
CASE WHEN DATA_TYPE = 'VARCHAR'
AND CHARACTER_MAXIMUM_LENGTH = -1
THEN CONCAT('CAST(LEFT(',CONCAT('[',COLUMN_NAME,']'),',2000) AS VARCHAR(2000)) AS [',COLUMN_NAME,']')
ELSE CONCAT('[',COLUMN_NAME,']')
END AS Colname, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = @TableSchema
) AS a
SET @SQLString3 = CONCAT(@SQLString1, @SQLString2, CHAR(13), 'FROM ', @TableSchema, '.',@TableName, CHAR(13), 'GO', CHAR(13), '-----------')
PRINT @SQLString3
SET @Counter +=1
END
February 9, 2024 at 7:55 pm
Except stay completely away from INFORMATION_SCHEMA views, which are more overhead anyway. Use sys.tables, sys.columns, etc., instead.
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".
February 10, 2024 at 9:10 pm
Thank you very much Scott and Ed.
First I trying to get the output and change to sys objects to see the difference.
Some of the tables are having more than 200 columns ... I have to change the following statement to max type to get all the columns....
But still some of the columns are missing ..
Please suggest..
SELECT @SQLString2 = STRING_AGG(CAST(ColName AS VARCHAR(MAX)),', ')
February 12, 2024 at 3:13 pm
I generated SQL separately for each view, even though that requires a cursor. In this specific case, I think you may want to be able to control things table by table. By default, the code just PRINTs the sql; when ready, set @exec_sql = 1 to actually run the sql and create the view(s).
--USE [your_db_name_goes_here]
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
DECLARE @column_name varchar(128);
DECLARE @exec_sql bit;
DECLARE @line_break varchar(10);
DECLARE @print_sql bit;
DECLARE @schema_name varchar(128);
DECLARE @table_id int;
DECLARE @table_name varchar(128);
DECLARE @view_code varchar(max);
SET @exec_sql = 0;
SET @print_sql = 1;
SET @line_break = CHAR(13) + CHAR(10);
IF @exec_sql = 0
SET @print_sql = 1;
DECLARE cursor_tables CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, t.object_id
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.max_length = -1
ORDER BY 1, 2
OPEN cursor_tables;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_tables INTO @schema_name, @table_name, @table_id;
IF @@FETCH_STATUS <> 0
BREAK;
SELECT @view_code = STUFF((
SELECT ', ' + CASE WHEN max_length = -1 THEN 'CAST(' + column_name + ' AS varchar(2000)) AS ' + column_name ELSE column_name END
FROM sys.columns
CROSS APPLY (
SELECT '[' + name + ']' AS column_name
) AS ca1
WHERE object_id = @table_id
ORDER BY column_id
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
, 1, 2, '')
SET @view_code = N'CREATE VIEW [' + @schema_name + '].[' + @table_name + '_view] ' + @line_break +
'AS ' + @line_break +
'SELECT ' + @view_code + ' FROM [' + @schema_name + '].[' + @table_name + ']; ' + @line_break +
'GO'
IF @print_sql = 1
PRINT @view_code;
IF @exec_sql = 1
EXEC(@view_code);
END /*WHILE*/CLOSE cursor_tables;
DEALLOCATE cursor_tables;
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".
February 12, 2024 at 7:39 pm
Thanks and appreciate your help Scott.
Added the drop command before the CREATE view command..
Even though the view_code variable declared as MAX it stores only 8000 characters...
Some view commands doesn't include all columns.
There are some tables the column names and data type string has more than 8000 characters..
....
February 12, 2024 at 8:07 pm
Did you try executing the code as well as printing? Print can only return 8k so it will truncate the variable if it's longer, but you can execute dynamic sql strings that are longer than 8k. Try returning LEN(@view_code) to see if it really is truncated.
I would limit the cursor to one table that you know has too many columns to print, set @exec_sql = 1 and run it. If it works, look at the view definition.
If you need a script to look at, you could define two more variables, set the first to left(@view_code,8000) and the other to something like right(@view_code,(len(@view_code)-7999)) then print them both (assuming you don't have any >16k).
February 12, 2024 at 10:13 pm
Thanks Ed
Adding left and right variable and print shows the full string info
Saved that script as a.sql and I would like to run it using sqlcmd by passing it
sqlcmd -S SYNSQL101\DEMOSYN -i a_wrapper.sql -o C:\PGCPS_SQLServer_Scripts\scripts\a.sql
it fails with this message
Msg 102, Level 15, State 1, Server SYNSQL101\DEMOSYN, Line 2
Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Server SYNSQL101\DEMOSYN, Line 4
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Server SYNSQL101\DEMOSYN, Line 7
Incorrect syntax near 'GO'.
February 13, 2024 at 6:41 pm
Hi Ed
Even running it directly on MSSM tool, it gives the error.
Please help on this issue.
CREATE VIEW [PGCPS_CUSTOM].[EGB_CC_SEQ_STANDARDS_VW]
AS
SELECT [ID], [CC_SEQ_ID], [STANDARD_ID], [SEQ], CAST([CONTENT] AS varchar(2000)) AS [CONTENT], [INSTRUCTION_DAYS], [START_DAY_NUM] FROM [rev].[EGB_CC_SEQ_STANDARDS];
GO
Msg 102, Level 15, State 1, Procedure EGB_CC_SEQ_STANDARDS_VW, Line 4 [Batch Start Line 2]
Incorrect syntax near 'GO'.
CREATE VIEW [PGCPS_CUSTOM].[EGB_CC_SETTINGS_VW]
AS
SELECT [ID], [CLASSID], CAST([GROUP_OBJ] AS varchar(2000)) AS [GROUP_OBJ], CAST([CONTACT_TEXT] AS varchar(2000)) AS [CONTACT_TEXT], CAST([COURSE_TEXT] AS varchar(2000)) AS [COURSE_TEXT], CAST([GRADING_TEXT] AS varchar(2000)) AS [GRADING_TEXT], CAST([POLICIES_TEXT] AS varchar(2000)) AS [POLICIES_TEXT], CAST([GB_CONFIGURATION] AS varchar(2000)) AS [GB_CONFIGURATION] FROM [rev].[EGB_CC_SETTINGS];
GO
Msg 102, Level 15, State 1, Procedure EGB_CC_SETTINGS_VW, Line 4 [Batch Start Line 2]
Incorrect syntax near 'GO'.
February 13, 2024 at 7:11 pm
Long shot: Have you changed the batch separator to something different than the default? That would produce the error you describe.
In SSMS check
Tools-> Options -> Query Execution -> General
and
Query... -> Execution --> General
February 13, 2024 at 7:37 pm
I found that a simple example generates the error above if you use CHAR(13) alone as a line break (which was in my suggested solution). Scott's solution used CHAR(13) + CHAR(10). Which did you use for the new line?
-- CHAR(13) alone fails
DECLARE @sql varchar(1000),
@linebreak VARCHAR(12)= CHAR(13)
SET @sql = concat('CREATE VIEW [dbo].[v1] AS SELECT 1 AS Col1', @linebreak, 'GO', @linebreak,'CREATE VIEW [dbo].[v2] AS SELECT 2 AS Col1',@linebreak, 'GO')
PRINT @sql
-- CHAR(13) + CHAR(10) succeeds
DECLARE @sql varchar(1000),
@linebreak VARCHAR(12)= CHAR(13)+CHAR(10)
SET @sql = concat('CREATE VIEW [dbo].[v1] AS SELECT 1 AS Col1', @linebreak, 'GO', @linebreak,'CREATE VIEW [dbo].[v2] AS SELECT 2 AS Col1',@linebreak, 'GO')
PRINT @sql
February 14, 2024 at 6:59 pm
Just remove the GO - there's no need for it.
SET @view_code = N'CREATE VIEW [' + 'PGCPS_CUSTOM' + '].[' + @table_name + '_VW] ' + @line_break +
'AS ' + @line_break +
'SELECT ' + @view_code + ' FROM [' + @schema_name + '].[' + @table_name + ']; '
IF @exec_sql = 1
EXEC(@view_code);
The EXEC() function will produce the error if there's a GO in the variable it executes.
You can easily try it yourself.
February 14, 2024 at 7:22 pm
CREATE VIEW must be in a batch by itself, i.e., without GOs, you could create only one view per script.
Since GOs are likely not valid, that may be what you have to do.
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".
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply