October 21, 2016 at 8:12 am
BLandry474 (10/21/2016)
... Right now this is all done by 'eyeball' and I thought if I had a good query that could examine a column and its length, then be able to trim the incoming contents and check its length - I could then produce a report showing what records just are not going to fit. This is basically what I am trying to address, along with wishing daily that the world used just one database!!...
If I understand correct you want a real flexible query to get the length of values in one or more columns of a specific (staging) table. The code below uses variables to build and execute a dynamic query. If the value of some column variables are not set, these columns are not added to the query. You can easily expand the query to inquire more columns. Optionally you can expand the dynamic query with a WHERE clause to filter only those records with long values.
declare @table nvarchar(128)
declare @column_1 nvarchar(128)
declare @column_2 nvarchar(128)
declare @column_3 nvarchar(128)
declare @sql_command nvarchar(max)
select @table = '{table}'
, @column_1 = '{column name}'
, @column_2 = '{column name}'
, @column_3 = '{column name}'
select @sql_command =
'SELECT ''' + quotename(@table) + '..' + quotename(@column_1) + ''' as column_name '
+ ', len (' + quotename(@column_1)+') as value_length '
-- add a second column
+ case when @column_2 IS NOT NULL THEN
', ''' + quotename(@table) + '..' + quotename(@column_2) + ''' as column_name '
+ ', len (' + quotename(@column_2)+') as value_length '
else '' end
-- add a third column
+ case when @column_3 IS NOT NULL THEN
', ''' + quotename(@table) + '..' + quotename(@column_3) + ''' as column_name '
+ ', len (' + quotename(@column_3)+') as value_length '
else '' end
+ ' from ' + quotename(@table)
execute(@sql_command)
In the above code the variables are set directly, but if this is not what you want you could modify it using the post from Luis (using sys.tables and sys.columns) to build it to the next level.
October 21, 2016 at 8:22 am
Hanshi,
Thanks for your reply, but unfortunately its way over-complex. I found a way to do this that is simple and elegant.
1. Run SP_COLUMNS and direct output to a Temp table.
2. Take the output and hit the Temp Table to get just the Table and Columns I want.
3. Use that filtered output as the comparator against the client table and values I want to check.
Sweet, simple, easy and it works with just a few lines of code.
"A Backhoe is a powerful and capable machine, except when you are trying to plant a single flower. Don't use a backhoe when a small shovel will do better..."
October 21, 2016 at 8:48 am
BLandry474 (10/21/2016)
Hanshi,Thanks for your reply, but unfortunately its way over-complex. I found a way to do this that is simple and elegant.
1. Run SP_COLUMNS and direct output to a Temp table.
2. Take the output and hit the Temp Table to get just the Table and Columns I want.
3. Use that filtered output as the comparator against the client table and values I want to check.
Sweet, simple, easy and it works with just a few lines of code.
"A Backhoe is a powerful and capable machine, except when you are trying to plant a single flower. Don't use a backhoe when a small shovel will do better..."
Can you post the code?
October 21, 2016 at 9:09 am
Alex,
Here is the basic code I used. This produces a nice temp table of information and of course, you can edit the query if you don't want all the columns. I am using this in a larger SProc that will allow my user to add the table he wants to compare as a parameter. Works well so far! Obviously, from this basic code, you could tinker it to meet your own needs.
Create table TempTable
(TABLE_QUALIFIER varchar(40),
TABLE_OWNER varchar(20),
TABLE_NAME varchar(40),
COLUMN_NAME varchar(40),
DATA_TYPE int,
TYPE_NAME varchar(20),
PREC int, LENGTH int,
SCALE int, RADIX int,
NULLABLE char(4),
REMARKS varchar(128),
COLUMN_DEF varchar(40),
SQL_DATA_TYPE int,
SQL_DATETIME_SUB int,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE char(4),
SS_DATA_TYPE int)
Set nocount on
Insert TempTable
Exec sp_columns @table_name = 'YOUR TABLE NAME'
Select * From TempTable
Drop table TempTable
October 21, 2016 at 11:33 am
BLandry474 (10/21/2016)
Alex,Here is the basic code I used. This produces a nice temp table of information and of course, you can edit the query if you don't want all the columns. I am using this in a larger SProc that will allow my user to add the table he wants to compare as a parameter. Works well so far! Obviously, from this basic code, you could tinker it to meet your own needs.
Create table TempTable(TABLE_QUALIFIER varchar(40),
TABLE_OWNER varchar(20),
TABLE_NAME varchar(40),
COLUMN_NAME varchar(40),
DATA_TYPE int,
TYPE_NAME varchar(20),
PREC int, LENGTH int,
SCALE int, RADIX int,
NULLABLE char(4),
REMARKS varchar(128),
COLUMN_DEF varchar(40),
SQL_DATA_TYPE int,
SQL_DATETIME_SUB int,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE char(4),
SS_DATA_TYPE int)
Set nocount on
Insert TempTable
Exec sp_columns @table_name = 'YOUR TABLE NAME'
Select * From TempTable
Drop table TempTable
Thanks,
We have something similar but we are using max(Datalength(fieldname)) on preloaded data to calculate if there are any empty spaces or if the field is over the limit.
October 21, 2016 at 10:47 pm
BLandry474 (10/20/2016)
Here is my brilliant idea (that I am sure someone else already thought of...)We have a situation where we regularly get tables from another department and we have to check every text-related column to see if the users actually "over-typed" something. What I mean is that they were looking away from the keyboard and typed more than 255 chars. without realizing it - saved the record - and never knew they had done this.
So the boss asked if I could write a SProc to detect this kind of thing. E.g. look at the columns, and see if they were "full" (for lack of a better term). So the first thing I did was to run:
EXEC sp_columns @table_name = 'WHATEVER_THE_TABLE_IS' and this gives me a nice display of ALL the internal makeup of the table - BUT!!! - I don't need all of it. I just need a few columns like LENGTH and PRECISION.
So here is the simple (I hope) question...
Can I develop a query that would allow me to specify the columns out of what I presume is Master? Again, I don't need all of it - just a few.
Thanks in advance for any help you can offer.
BLandry474 (10/21/2016)
My thanks to all who responded, but please, in the future, read the question - I DID NOT say "Users are entering data", what I said was "We get tables" - meaning these are tables exported from other systems where the limits are not the same as SQL Server. So a few of you got off on a tangent answering a question I never asked.To those who presented good, workable solutions, many thanks - it's most helpful. To the others, I honor your excellent knowledge; you're way ahead of me - but deep knowledge is kind of useless if you're answering a question that was in fact, never asked.
No offense intended here to anyone, but as the wise man says; "intelligence is useless without common sense."
Heh... no offense but why didn't you just ask how you could get the same information as what sp_columns gives you but in a SELECT instead of going 'round the horn with information that only served to confuse those in a hurry to help and then bawling people out for trying to solve the root problem, which is actually a bad app that you can't fix anyway? 😉
Getting to the root of your problem...
BLandry474 (10/21/2016)
Alex,Here is the basic code I used. This produces a nice temp table of information and of course, you can edit the query if you don't want all the columns. I am using this in a larger SProc that will allow my user to add the table he wants to compare as a parameter. Works well so far! Obviously, from this basic code, you could tinker it to meet your own needs.
Create table TempTable(TABLE_QUALIFIER varchar(40),
TABLE_OWNER varchar(20),
TABLE_NAME varchar(40),
COLUMN_NAME varchar(40),
DATA_TYPE int,
TYPE_NAME varchar(20),
PREC int, LENGTH int,
SCALE int, RADIX int,
NULLABLE char(4),
REMARKS varchar(128),
COLUMN_DEF varchar(40),
SQL_DATA_TYPE int,
SQL_DATETIME_SUB int,
CHAR_OCTET_LENGTH int,
ORDINAL_POSITION int,
IS_NULLABLE char(4),
SS_DATA_TYPE int)
Set nocount on
Insert TempTable
Exec sp_columns @table_name = 'YOUR TABLE NAME'
Select * From TempTable
Drop table TempTable
... that's nice...
There are easier ways though. There are a whole bunch of system tables in the "sys." schema of every database that contain such information and much more. They're in "Books Online" and you should take the time to look some of them up and as well as some of the meta-data functions. It'll make any future such projects a whole lot easier.
Here's one example...
--===== Uncomment the "ANDs" to isolate an individual table.
SELECT SchemaName = OBJECT_SCHEMA_NAME(object_id)
,ObjectName = OBJECT_NAME(object_id)
,ColumnName = name
,* --change this to whatever column names you need
FROM sys.columns
WHERE OBJECTPROPERTY(object_id,'IsTable') = 1
AND OBJECTPROPERTY(object_id,'IsMSShipped') = 0
--AND OBJECT_SCHEMA_NAME(object_id) = 'dbo' --or whatever
--AND OBJECT_NAME(object_id) = 'WHATEVER_THE_TABLE_IS' --obviously, you'll need to change this.
ORDER BY SchemaName,ObjectName,ColumnName
;
Perhaps an easier thing would be in the INFORMATION_SCHEMA views, which you can also read about in "Books Online". Here's an example.
SELECT * --Change the "*" to whatever column names you want
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' -- or whatever
AND TABLE_NAME = 'WHATEVER_THE_TABLE_IS' --obviously, you'll need to change this
;
If you don't know what "Books Online" is, you can download it from the following URL and then you can get help just by pressing the {f1} key.
[font="Arial Black"]Books Online Download for Local Installation[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2016 at 6:09 am
Jeff - Look at the fourth and sixth small paragraph of my original post. I did exactly what you are suggesting I did not do. I asked to get sp_column-style output.
Many replies on SSC ask what any given poster is "doing" - instead of focusing on what a poster wants to do. I need to get work done, not explain what our business does. And yes, this can be very frustrating - for that I apologize.
I know you are a SQL expert and I have seen your posts for years, however it might be good to remember that for many of us asking questions, SQL is a small part of what we do - as is the case in my work. I apologize for looking for quick help, but I thought that was at least part of the idea behind the SSC forums.
October 25, 2016 at 1:44 pm
I DID NOT say "Users are entering data", what I said was "We get tables" - meaning these are tables exported from other systems where the limits are not the same as SQL Server. So a few of you got off on a tangent answering a question I never asked.
You may not have literally typed that, but your description certainly indicates that users enter this :
We have a situation where we regularly get tables from another department and we have to check every text-related column to see if the users actually "over-typed" something. What I mean is that they were looking away from the keyboard and typed more than 255 chars. without realizing it - saved the record - and never knew they had done this.
I wouldnt get worked up about people trying to help you certainly. The helpers need to make certain they understand your situation and what they are dealing with.
----------------------------------------------------
October 25, 2016 at 1:56 pm
Sterling Ostin (10/24/2016)
Jeff - Look at the fourth and sixth small paragraph of my original post. I did exactly what you are suggesting I did not do. I asked to get sp_column-style output.Many replies on SSC ask what any given poster is "doing" - instead of focusing on what a poster wants to do. I need to get work done, not explain what our business does. And yes, this can be very frustrating - for that I apologize.
I know you are a SQL expert and I have seen your posts for years, however it might be good to remember that for many of us asking questions, SQL is a small part of what we do - as is the case in my work. I apologize for looking for quick help, but I thought that was at least part of the idea behind the SSC forums.
And sometimes we try to provide a better solution than the one requested. That is part of what makes this site better than the others. We don't just "give you an answer," we try to give you more so that you can become better at what you do. that is what this site did for me years ago and why I try to help others now.
October 25, 2016 at 1:57 pm
Point taken, and most of the time, good advice. Thanks for offering it!
October 25, 2016 at 4:34 pm
Sterling Ostin (10/24/2016)
Jeff - Look at the fourth and sixth small paragraph of my original post. I did exactly what you are suggesting I did not do. I asked to get sp_column-style output.Many replies on SSC ask what any given poster is "doing" - instead of focusing on what a poster wants to do. I need to get work done, not explain what our business does. And yes, this can be very frustrating - for that I apologize.
I know you are a SQL expert and I have seen your posts for years, however it might be good to remember that for many of us asking questions, SQL is a small part of what we do - as is the case in my work. I apologize for looking for quick help, but I thought that was at least part of the idea behind the SSC forums.
Hm,
You did not even bother to read what Luis posted.
He's got you a ready-made solution, 2 steps ahead of what you pasted .
_____________
Code for TallyGenerator
October 25, 2016 at 6:46 pm
Sterling Ostin (10/24/2016)
Jeff - Look at the fourth and sixth small paragraph of my original post. I did exactly what you are suggesting I did not do. I asked to get sp_column-style output.Many replies on SSC ask what any given poster is "doing" - instead of focusing on what a poster wants to do. I need to get work done, not explain what our business does. And yes, this can be very frustrating - for that I apologize.
I know you are a SQL expert and I have seen your posts for years, however it might be good to remember that for many of us asking questions, SQL is a small part of what we do - as is the case in my work. I apologize for looking for quick help, but I thought that was at least part of the idea behind the SSC forums.
Understood. Please realize that we sometimes want to help the poster fix the root problem rather than put the proverbial Band-Aid on a stab wound.
The real key here is that you really needed to be able to get output similar to what sp_Columns provided. You found one way and shared that and that's a part of what makes this site great. Another part of what makes it great are the discussions as to why someone is trying to do something so that we can help them avoid doing things "the hard way". There have been many times where the OP has come back and said something like "Holy Moly! I had no clue!".
BTW, did you have a look at the two alternate solutions to your original problem that I posted? Might come in hand for future things with having the need to build a table first.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 26, 2016 at 5:38 am
Well said, and understood - all good point. And yes, the solution I found actually came from a number of members, yourself included.
Thanks for that!
October 26, 2016 at 5:55 am
Sergiy,
I did read Luis' post and incorporated it in an even more graceful and shorter solution that best suited my need.
In the future, maybe you ought to question what you presume you "know" about other people and their actions.
🙂
October 26, 2016 at 3:49 pm
Sterling Ostin (10/26/2016)
Sergiy,I did read Luis' post and incorporated it in an even more graceful and shorter solution that best suited my need.
In the future, maybe you ought to question what you presume you "know" about other people and their actions.
🙂
Heh... careful now. You don't have the time for this. Gotta trust me on this one. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply