SQL syntax issue - some troubleshooting help.
-
Below is a create statement of the table taken by using SSMS ( Right click, Script Table as, Create to )...
Listing 1
CREATE TABLE [HEDIS_TO_HAP].[provider_crosswalk](
[PTYPE] [varchar](10) NULL,
[DESCR] [varchar](300) NULL,
[SPEC1] [varchar](10) NULL,
[SPEC2] [varchar](10) NULL,
[SPEC3] [varchar](10) NULL
) ON [PRIMARY]Now I am writing a script that will give me the col names and data types. ( that is below )
Question: For some unknown reason, when I select this particular table , it lists each column 2 times ( BTW, I am using someone elses code ) ...
So help me pinpoint what it is listing each column 2 times ...Let me first show you the output ....
[PTYPE] [varchar](10) NULL,
[PTYPE] [varchar](10) NULL,
[DESCR] [varchar](300) NULL,
[DESCR] [varchar](300) NULL,
[SPEC1] [varchar](10) NULL,
[SPEC1] [varchar](10) NULL,
[SPEC2] [varchar](10) NULL,
[SPEC2] [varchar](10) NULL,
[SPEC3] [varchar](10) NULL
[SPEC3] [varchar](10) NULLNow here is the script that generates the above output
Declare @tableName sysname = 'provider_crosswalk' --The table name for which the script will be genrated else for all tables
Declare @schemaName sysname = 'HEDIS_TO_HAP' --Specify the schema name else table of same name from all schema will be generated
DECLARE @MaxColOrder INT
DECLARE @cols VARCHAR (MAX) = '', @default VARCHAR (MAX) = '', @check VARCHAR (MAX) = '', @fk VARCHAR (MAX) = '', @idx VARCHAR (MAX) = ''
DECLARE @var varchar (max)SELECT
@MaxColOrder = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = @schemaName;WITH cteCol as (SELECT
QUOTENAME(sys_col.name) + SPACE(1) + (SELECT TOP 1
CASE
WHEN sys_types.is_user_defined = 1 THEN QUOTENAME(SCHEMA_NAME(sys_types.schema_id)) + '.' + QUOTENAME(sys_types.name) ELSE QUOTENAME(sys_types.name)
END
FROM sys.types sys_types
WHERE sys_col.system_type_id = sys_types.system_type_id AND sys_types.user_type_id = sys_col.user_type_id)
+ CASE (SELECT TOP 1
sys_types.name
FROM sys.types sys_types
WHERE sys_col.system_type_id = sys_types.system_type_id)
WHEN 'decimal' THEN '(' + CAST(sys_col.precision AS varchar(10)) + ', ' + CAST(sys_col.scale AS varchar(10)) + ')'
WHEN 'numeric' THEN '(' + CAST(sys_col.precision AS varchar(10)) + ', ' + CAST(sys_col.scale AS varchar(10)) + ')'
WHEN 'sql_variant' THEN ''
WHEN 'text' THEN ''
WHEN 'ntext' THEN ''
WHEN 'xml' THEN '' ELSE COALESCE(
CASE
WHEN sys_col.max_length = -1 THEN '(MAX)'
WHEN sys_col.user_type_id <> sys_col.system_type_id THEN '' ELSE (SELECT TOP 1
ISNULL('(' + CAST(inf_cols.CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')', '')
FROM INFORMATION_SCHEMA.COLUMNS inf_cols
WHERE inf_tbl.TABLE_SCHEMA = inf_cols.TABLE_SCHEMA AND inf_tbl.TABLE_NAME = inf_cols.TABLE_NAME AND sys_col.name = inf_cols.COLUMN_NAME)
END, '')
END
+ SPACE(1) + CASE
WHEN sys_col.is_identity = 1 THEN (SELECT TOP 1
'IDENTITY(' + CAST(ISNULL(sys_ident.seed_value, 0) AS varchar(10)) + ', ' + CAST(ISNULL(sys_ident.increment_value, 1) AS varchar(10)) + ')'
FROM sys.identity_columns sys_ident
WHERE sys_ident.name = sys_col.name AND sys_col.object_id = sys_ident.object_id AND sys_col.is_identity = 1)
ELSE ''
END
+ SPACE(1) + CASE
WHEN sys_col.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL'
END + CASE
WHEN @MaxColOrder = sys_col.column_id THEN '' ELSE ', '
END AS cols,
sys_col.column_id
FROM INFORMATION_SCHEMA.TABLES inf_tbl
INNER JOIN sys.columns sys_col
ON inf_tbl.TABLE_NAME = OBJECT_NAME(sys_col.object_id)
WHERE inf_tbl.TABLE_NAME = @tableName AND inf_tbl.TABLE_SCHEMA = @schemaName
)SELECT
@cols = COALESCE(@cols, '') + ISNULL(CHAR(13) + CHAR(10) + c.cols, '')
FROM cteCol c
ORDER BY COLUMN_IDSELECT
@var = @var + @cols + '
)'
+ CHAR(13) + CHAR(10) +
'
GO
'
+ CHAR(13) + CHAR(10)Select @cols
Question: What is wrong with the code.. Why it is listing each col 2 times for this particular table ?
-
I can not recreate the problem you are reporting. I have run your code, after creating the needed schema, and I do not get any duplicates.
- Lynn Pettis - Friday, January 27, 2017 10:31 AM
Very interesting, yes it works fine on other tables but not in one particular one.... I wonder why ? .... I made the code even simple take a look at what I have below. You can just plug in any table name and schema name and try to see what comes.
Just one SELECT statement now.....
SELECT
QUOTENAME(sys_col.name) + SPACE(1) + (SELECT TOP 1
CASE
WHEN sys_types.is_user_defined = 1 THEN QUOTENAME(SCHEMA_NAME(sys_types.schema_id)) + '.' + QUOTENAME(sys_types.name) ELSE QUOTENAME(sys_types.name)
END
FROM sys.types sys_types
WHERE sys_col.system_type_id = sys_types.system_type_id AND sys_types.user_type_id = sys_col.user_type_id)
+
CASE (SELECT TOP 1
sys_types.name
FROM sys.types sys_types
WHERE sys_col.system_type_id = sys_types.system_type_id)
WHEN 'decimal' THEN '(' + CAST(sys_col.precision AS varchar(10)) + ', ' + CAST(sys_col.scale AS varchar(10)) + ')'
WHEN 'numeric' THEN '(' + CAST(sys_col.precision AS varchar(10)) + ', ' + CAST(sys_col.scale AS varchar(10)) + ')'
WHEN 'sql_variant' THEN ''
WHEN 'text' THEN ''
WHEN 'ntext' THEN ''
WHEN 'xml' THEN '' ELSE COALESCE(
CASE
WHEN sys_col.max_length = -1 THEN '(MAX)'
WHEN sys_col.user_type_id <> sys_col.system_type_id THEN '' ELSE (SELECT TOP 1
ISNULL('(' + CAST(inf_cols.CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')', '')
FROM INFORMATION_SCHEMA.COLUMNS inf_cols
WHERE inf_tbl.TABLE_SCHEMA = inf_cols.TABLE_SCHEMA AND inf_tbl.TABLE_NAME = inf_cols.TABLE_NAME AND sys_col.name = inf_cols.COLUMN_NAME)
END, '')
END
+ SPACE(1) + CASE
WHEN sys_col.is_identity = 1 THEN (SELECT TOP 1
'IDENTITY(' + CAST(ISNULL(sys_ident.seed_value, 0) AS varchar(10)) + ', ' + CAST(ISNULL(sys_ident.increment_value, 1) AS varchar(10)) + ')'
FROM sys.identity_columns sys_ident
WHERE sys_ident.name = sys_col.name AND sys_col.object_id = sys_ident.object_id AND sys_col.is_identity = 1)
ELSE ''
END
+ SPACE(1) + CASE
WHEN sys_col.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL'
END + CASE
WHEN 5 = sys_col.column_id THEN '' ELSE ', '
END AS cols,
sys_col.column_id
FROM INFORMATION_SCHEMA.TABLES inf_tbl
INNER JOIN sys.columns sys_col
ON inf_tbl.TABLE_NAME = OBJECT_NAME(sys_col.object_id)
WHERE inf_tbl.TABLE_NAME = 'provider_crosswalk' AND inf_tbl.TABLE_SCHEMA = 'HEDIS_TO_HAP' - mw112009 - Friday, January 27, 2017 10:38 AM
I am sorry ... You also have to replace the 5 ( in the code ) with the max number of columns .......So where is says "WHEN 5 = sys_col.column_id THEN '' ELSE ', '" The 5 must be replaced.
Anyhow I found a quick solution to this ... That was easy ...... ALL THAT I did was add the word "DISTINCT" next to the SELECT and it works ......!!!!
SELECT DISTINCT
QUOTENAME(sys_col.name) + SPACE(1) + (SELECT TOP 1
CASE
WHEN sys_types.is_user_defined = 1 THEN QUOTENAME(SCHEMA_NAME(sys_types.schema_id)) + '.' + QUOTENAME(sys_types.name) ELSE QUOTENAME(sys_types.name)
END
FROM sys.types sys_types
WHERE sys_col.system_type_id = sys_types.system_type_id AND sys_types.user_type_id = sys_col.user_type_id)
+
CASE (SELECT TOP 1
sys_types.name
FROM sys.types sys_types
WHERE sys_col.system_type_id = sys_types.system_type_id)
WHEN 'decimal' THEN '(' + CAST(sys_col.precision AS varchar(10)) + ', ' + CAST(sys_col.scale AS varchar(10)) + ')'
WHEN 'numeric' THEN '(' + CAST(sys_col.precision AS varchar(10)) + ', ' + CAST(sys_col.scale AS varchar(10)) + ')'
WHEN 'sql_variant' THEN ''
WHEN 'text' THEN ''
WHEN 'ntext' THEN ''
WHEN 'xml' THEN '' ELSE COALESCE(
CASE
WHEN sys_col.max_length = -1 THEN '(MAX)'
WHEN sys_col.user_type_id <> sys_col.system_type_id THEN '' ELSE (SELECT TOP 1
ISNULL('(' + CAST(inf_cols.CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')', '')
FROM INFORMATION_SCHEMA.COLUMNS inf_cols
WHERE inf_tbl.TABLE_SCHEMA = inf_cols.TABLE_SCHEMA AND inf_tbl.TABLE_NAME = inf_cols.TABLE_NAME AND sys_col.name = inf_cols.COLUMN_NAME)
END, '')
END
+ SPACE(1) + CASE
WHEN sys_col.is_identity = 1 THEN (SELECT TOP 1
'IDENTITY(' + CAST(ISNULL(sys_ident.seed_value, 0) AS varchar(10)) + ', ' + CAST(ISNULL(sys_ident.increment_value, 1) AS varchar(10)) + ')'
FROM sys.identity_columns sys_ident
WHERE sys_ident.name = sys_col.name AND sys_col.object_id = sys_ident.object_id AND sys_col.is_identity = 1)
ELSE ''
END
+ SPACE(1) + CASE
WHEN sys_col.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL'
END + CASE
WHEN 5 = sys_col.column_id THEN '' ELSE ', '--replace 5 with the max number of cols
END AS cols,
sys_col.column_id
FROM INFORMATION_SCHEMA.TABLES inf_tbl
INNER JOIN sys.columns sys_col
ON inf_tbl.TABLE_NAME = OBJECT_NAME(sys_col.object_id)
WHERE inf_tbl.TABLE_NAME = 'provider_crosswalk' AND inf_tbl.TABLE_SCHEMA = 'HEDIS_TO_HAP' - mw112009 - Friday, January 27, 2017 10:43 AM
Found another issue....
If you can fix great ....... Something is wrong with the joins below.. See the output attached.... One column should only have one column_id. Can you fix it ....
Select DISTINCT
column_id, name
FROM INFORMATION_SCHEMA.TABLES inf_tbl
INNER JOIN sys.columns sys_col
ON inf_tbl.TABLE_NAME = OBJECT_NAME(sys_col.object_id)
WHERE inf_tbl.TABLE_NAME = 'xxxTmpClaims' AND inf_tbl.TABLE_SCHEMA = 'HEDIS_TO_HAP'
ORDER BY 2This is the output
- mw112009 - Friday, January 27, 2017 11:17 AMmw112009 - Friday, January 27, 2017 10:43 AM
Found another issue....
If you can fix great ....... Something is wrong with the joins below.. See the output attached.... One column should only have one column_id. Can you fix it ....
Select DISTINCT
column_id, name
FROM INFORMATION_SCHEMA.TABLES inf_tbl
INNER JOIN sys.columns sys_col
ON inf_tbl.TABLE_NAME = OBJECT_NAME(sys_col.object_id)
WHERE inf_tbl.TABLE_NAME = 'xxxTmpClaims' AND inf_tbl.TABLE_SCHEMA = 'HEDIS_TO_HAP'
ORDER BY 2This is the output
Found... the solution ... The correct joins should be as follows...
The above incorrect output comes because the table xxxTmpClaims cane be found 3 times ( in 3 different schemas )Found... the solution ... We need to use this joins in the code ......DONE!
Select sys_col.column_id, sys_col.name
FROM
SYS.tables T
INNER JOIN sys.schemas SC on ( SC.schema_id = T.Schema_id )
INNER JOIN sys.columns sys_col on ( sys_col.object_id = T.object_id )
INNER JOIN INFORMATION_SCHEMA.TABLES inf_tbl on (( inf_tbl.TABLE_NAME = T.name ) AND (inf_tbl.TABLE_SCHEMA = SC.name) )
WHERE
inf_tbl.TABLE_NAME = 'xxxTmpClaims'
AND
inf_tbl.TABLE_SCHEMA = 'HEDIS_TO_HAP'
ORDER BY 1 - I haven't fully gone through the queries because I feel lazy right now but I played around with this and was able to get duplicates if I have two of the tables in two different schemas.
From what I can tell, you aren't filtering on the schema in sys.columns so you have two sets of those columns. Off the top of my head, when I filtered sys.columns on the schema it was fine.
For the first issue, at the end of the CTE definition add:
AND OBJECT_SCHEMA_NAME (object_id, DB_ID() ) = @schemaNameIn your second query, remove the distinct and add the following at the end:
AND OBJECT_SCHEMA_NAME (object_id, DB_ID() ) = 'HEDIS_TO_HAP'Probably a better way to write all that but that's how I got around the issue.
Sue
-
The other suggestion I have is use the system views sys.schemas, sys.tables, sys.columns, sys.types instead of the INFORMATIOIN_SCHEMA views.
- Lynn Pettis - Friday, January 27, 2017 12:42 PM
Yup....mixing system views and information schema views just doesn't seem right. System views have more information and in this case, sys.tables has schema_id.
The second query seems more straightforward with just system views:SELECT
column_id, c.name
FROM sys.tables t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
WHERE t.name = 'xxxTmpClaims'
AND t.schema_id = SCHEMA_ID('HEDIS_TO_HAP')
ORDER BY c.nameSue
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply