February 13, 2018 at 12:22 am
Hi Experts,
Can you all help me out to find a query in which i can find all the tables in a particular Database which has numeric values with 3 decimals present ; situation is that I need to round off the decimal values to two for the numbers which has 3 decimal values.
Any help on this will be most helpful
Regards,
Adil
February 13, 2018 at 2:44 am
adilahmed1989 - Tuesday, February 13, 2018 12:22 AMHi Experts,Can you all help me out to find a query in which i can find all the tables in a particular Database which has numeric values with 3 decimals present ; situation is that I need to round off the decimal values to two for the numbers which has 3 decimal values.
Any help on this will be most helpful
Regards,
Adil
Hereby request to kindly help me out with this quickly as we are running under the water and desperate need any kind of solution.
Sincerely
AD
February 13, 2018 at 4:15 am
This was removed by the editor as SPAM
February 13, 2018 at 4:47 am
Something like this would show you the tables and rows where a column holds data that has at least 3 decimal places: -
DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = STUFF((SELECT CHAR(13) + CHAR(10) + 'SELECT ' + CHAR(39) + [o].[name] + CHAR(39) + ' AS [Table_Name], * FROM ['
+ OBJECT_SCHEMA_NAME([o].[object_id]) + '].[' + OBJECT_NAME([o].[object_id]) + '] WHERE ' + [ca].[val] + ';'
FROM [sys].[objects] [o]
CROSS APPLY ( SELECT STUFF((SELECT 'OR (((abs([' + [c].[name] + '])*100) - CONVERT(BIGINT,(abs([' + [c].[name] + '])*100))) <> 0)'
FROM [sys].[columns] [c]
INNER JOIN [sys].[types] [t] ON [t].[system_type_id] = [c].[system_type_id]
WHERE [t].[name] IN ( 'real', 'float', 'decimal', 'numeric' )
AND [o].[object_id] = [c].[object_id]
FOR XML PATH(''),
TYPE
).[value]('.', 'NVARCHAR(MAX)'), 1, 3, '')
) [ca] ( [val] )
WHERE [o].[type] = 'U'
AND [ca].[val] IS NOT NULL
FOR XML PATH(''),
TYPE).[value]('.', 'NVARCHAR(MAX)'), 1, 2, '');
EXECUTE [sys].[sp_executesql] @SQL;
February 13, 2018 at 5:02 am
adilahmed1989 - Tuesday, February 13, 2018 2:44 AMadilahmed1989 - Tuesday, February 13, 2018 12:22 AMHi Experts,Can you all help me out to find a query in which i can find all the tables in a particular Database which has numeric values with 3 decimals present ; situation is that I need to round off the decimal values to two for the numbers which has 3 decimal values.
Any help on this will be most helpful
Regards,
AdilHereby request to kindly help me out with this quickly as we are running under the water and desperate need any kind of solution.
Sincerely
AD
I don't know of a way to validate the precision of the data that is stored in the DB.
However, the following script will get you a list of Tables and Columns that can store data with a precision of 3 or more.
SELECT
SchemaName = s.name
, TableName = t.name
, ColumnName = c.name
, [DataType] = st.name
+ CASE WHEN st.name IN ('decimal', 'numeric')
THEN ' (' + CONVERT(VARCHAR(10), c.[precision]) + ', ' + CONVERT(VARCHAR(10), c.scale) + ')'
ELSE ''
END
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns AS c
ON t.object_id = c.object_id
INNER JOIN sys.types AS st
ON c.user_type_id = st.user_type_id
WHERE t.is_ms_shipped = 0
AND ((st.name IN ( 'numeric', 'decimal', 'money', 'smallmoney' ) AND c.scale >= 3)
OR st.name IN ( 'float', 'real' )
)
ORDER BY s.name, t.name, c.name;
February 20, 2018 at 5:03 am
adilahmed1989 - Tuesday, February 13, 2018 2:44 AMadilahmed1989 - Tuesday, February 13, 2018 12:22 AMHi Experts,Can you all help me out to find a query in which i can find all the tables in a particular Database which has numeric values with 3 decimals present ; situation is that I need to round off the decimal values to two for the numbers which has 3 decimal values.
Any help on this will be most helpful
Regards,
AdilHereby request to kindly help me out with this quickly as we are running under the water and desperate need any kind of solution.
Sincerely
AD
I'd like to see the queries that you'd been working on !
February 20, 2018 at 5:51 am
adilahmed1989 - Tuesday, February 13, 2018 2:44 AMadilahmed1989 - Tuesday, February 13, 2018 12:22 AMHi Experts,Can you all help me out to find a query in which i can find all the tables in a particular Database which has numeric values with 3 decimals present ; situation is that I need to round off the decimal values to two for the numbers which has 3 decimal values.
Any help on this will be most helpful
Regards,
AdilHereby request to kindly help me out with this quickly as we are running under the water and desperate need any kind of solution.
Sincerely
AD
So, you've been offered three answers to your original post, which should at least give you a head start on your project.
I'm curious. Why would you be in such difficulties and so badly in need of a resolution?
Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply