Limit length of all columns in SELECT results-CodePudding

I'm struggling every day with 1035 tables in a database and every table has many columns. Some columns are quite long although it was not necessary to be so long.

For example in the table Messages which contains the emails, the columns MailTo and MailFrom are nvarchar(400) or the Subject is defined as nvarchar(500).

Suppose that I want to see the table using a simple query:

SELECT TOP 100 * 
FROM Messages;

but most columns have long length and they do not fit to the monitor. I must scroll left and right to see the table correctly and because the scrollbars are not thumb-tracking it is very boring.

I can use the select query like:

SELECT TOP 100
    LEFT(Subject, 20) as Subject_,
    LEFT(MailFrom, 20) as MailFrom_,
    LEFT(MailTo, 20) as MailTo_,
    Deleted, DeletedByUser, Status, MsgType, *
FROM Messages;

to be able to see the table better, but the problem is that I do not want to write such a complicated select statement every time that I want just seeing each table.

It would be much simpler for me to see the tables like this:

SELECT TOP 100 LimitLength(*, 20) 
FROM A_Table_Name;

because I use select statement many times a day to see the database.

By the way, I have to use a software called SQL-Spy which my company has written to be able to access the database. This software is written by Delphi and uses a simple TDBGrid to show the tables.

CodePudding user response：

If you are using sql server management studio, you can at minimum set the characters retrieved to 30 characters for Non-XML data like given below, in the Tools > Options

When you run below command, only the left 30 characters will be displayed.

SELECT * FROM Customer

Note: In future, you can revert back to original column width settings by changing column width to 65535 or by clicking Reset to Default button in the Tools > Options. But, Rest to Default button will reset all settings. So, take care of it.

CodePudding user response：

What you could do is create view to make life easier:

CREATE VIEW  MessagesView AS
SELECT TOP 100
    LEFT(Subject, 20) as Subject_,
    LEFT(MailFrom, 20) as MailFrom_,
    LEFT(MailTo, 20) as MailTo_,
    Deleted, DeletedByUser, Status, MsgType, *
FROM Messages;

Then you can just easily do a:

SELECT * FROM MessagesView

That way you only need to make the view once and have a simple statement to query your view.