December 1, 2015 at 4:43 am
Hi,
I am trying to solve very basic query construct.
I need to know the allocated size of the columns in our tables and Check the max length of the largest data held in the column against it. This is because as a quick fix to a problem we had for loading a constantly changing data feed we had to allow enough space for every column so that our nightly load would not fail. Now we are trying to make the process more efficient and we need to reduce these large spaces. I have a query that gives me the table name, column name, DataType and Size. I need another column that shows me the size of the largest piece of data held in each column.
I hope this makes sense.
Here is my code:
SELECT T.NAME AS
, C.NAME AS [COLUMN NAME]
, P.NAME AS [DATA TYPE]
, P.MAX_LENGTH AS
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE';
December 1, 2015 at 6:41 am
ok, this worked pretty well for me;
you can expand this to use char/nchar if needed, but i left it as varchar/nvarchar, as that's all i'd ever use anyway.
no need to check ints or decimals values, the size is the size.
i used FOR XML to build a query, per table, so i can find both the max current length and the current definition.
it produces a query like this(formatted for readbility)
SELECT Max(Len([DatabaseName])) AS [DatabaseName],
'256' AS [CurMaxDatabaseName],
Max(Len([SchemaName])) AS [SchemaName],
'256' AS [CurMaxSchemaName],
Max(Len([ObjectName])) AS [ObjectName],
'256' AS [CurMaxObjectName],
Max(Len([IndexName])) AS [IndexName],
'256' AS [CurMaxIndexName],
Max(Len([StatisticsName])) AS [StatisticsName],
'256' AS [CurMaxStatisticsName],
Max(Len([Command])) AS [Command],
'-1' AS [CurMaxCommand],
Max(Len([CommandType])) AS [CommandType],
'120' AS [CurMaxCommandType],
Max(Len([ErrorMessage])) AS [ErrorMessage],
'-1' AS [CurMaxErrorMessage]
FROM [CommandLog]
and my query generator:
SELECT DISTINCT
schema_name(t.schema_id) As SchemaName,
t.name,
'SELECT ' + sq.Columns + ' FROM ' + schema_name(t.schema_id) + '.' + quotename(name)
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',MAX(LEN(' + quotename(name) +')) As ' + quotename(name) + ','
+ CASE
WHEN max_length = -1
THEN '''' + convert(varchar,max_length /2) + ''''
WHEN type_name(system_type_id) = ' nvarchar'
THEN '''' + convert(varchar,max_length /2) + ''''
ELSE '''' + convert(varchar,max_length) + ''''
END + ' AS [CurMax' + name + ']'
FROM sys.columns sc
WHERE sc.object_id = s.object_id
AND type_name(system_type_id) IN('varchar','nvarchar')
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
Lowell
December 2, 2015 at 3:14 am
Hi Lowell,
Thank you very much for your help. This has moved me towards the right direction. I have slightly modified your query to incorporate a cursor to execute the query. I still need to add an extra bit to
unpivot results and insert it into tables to reference them at a different time.
Here is where I am so far.
DECLARE @QueryToRun varchar(Max)
DECLARE @sqlCommand varchar(MAX)
DECLARE CursorQuery CURSOR -- Declare cursor
LOCAL SCROLL STATIC
FOR
-------MAIN DATA
SELECT DISTINCT
'SELECT ' + sq.Columns + ' FROM ' + SCHEMA_NAME(t.SCHEMA_ID) + '.' + QUOTENAME(name) AS QueryToRun
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',MAX(LEN(' + QUOTENAME(name) +')) As ' + QUOTENAME(name) --+ ','
FROM sys.columns sc
WHERE sc.OBJECT_ID = s.OBJECT_ID
AND TYPE_NAME(system_type_id) IN('varchar','nvarchar')
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq
ON t.OBJECT_ID = sq.OBJECT_ID
WHERE t.name LIKE 'tblSR%'---We are only intrested in System one tables and they start with tblSR...
------
OPEN CursorQuery -- open the cursor
FETCH NEXT FROM CursorQuery
INTO @sqlCommand
EXEC (@sqlCommand)
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CursorQuery
INTO @sqlCommand
EXEC (@sqlCommand)
END
CLOSE CursorQuery -- close the cursor
DEALLOCATE CursorQuery -- Deallocate the cursor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply