Journey to SQL Authority with Pinal Dave

Posted in Pinal Dave, SQL, SQL Authority, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 28 Comments

28 Responses

1. on November 14, 2007 at 5:37 pm | Reply Navab

   Really useful

   
   
2. on November 14, 2007 at 7:29 pm | Reply pergh

   Just what I wondered about.

   
   
3. on November 14, 2007 at 8:58 pm | Reply Poov

   Hi, It is really useful.

   I have the database – in which i have to generate script for 10 tables only – as i dont have Enterprise Manager, i want to generate the script through Query Analyser onl.

   ie the script should like the result of –Enterprise Manager—> Select the Table(s)–Rt Click–All Tasks — Generate SQL Script.

   How can i get this?

   Thanks in Advance.

   Regards — Poov

   
   
4. on January 18, 2008 at 5:18 pm | Reply Heidi

   Thnx!!
   This was just what I needed
   xx

   
   
5. on February 7, 2008 at 3:17 am | Reply Dave

   Thank you! Before this I found a bunch of other SQL’s _languages_ that accept just LENGTH, but THIS is what I really needed.

   Thank you!

   
   
6. on February 17, 2008 at 11:55 pm | Reply BADRI

   This is what I was looking for.
   Thanks

   
   
7. on March 28, 2008 at 8:07 pm | Reply Andrew Cullis

   Nice …

   but now how would i do that in Hibernate …

   : )

   
   
8. on June 22, 2008 at 3:12 am | Reply Ben Kimball

   Thanks for the info! Just what I was looking for…

   
   
9. on July 4, 2008 at 7:40 am | Reply Alain

   Thanks :) You saved me lots of trouble…

   
   
10. on July 16, 2008 at 9:16 pm | Reply Antonio

    I need some statement to check database field size in the code before inserting any value

    
    
11. on July 17, 2008 at 7:46 am | Reply Imran Mohammed

    @Antonio,

    You can use this stord procedure

    SP_HELP table_name

    it will list out all the fields, their sizes, constraints, indexes….

    It gives very good information.

    Hope this helps.

    Thanks,
    Imran.

    
    
12. on August 11, 2008 at 11:57 am | Reply anshul jindal

    thanks , very useful

    
    
13. on November 26, 2008 at 11:32 pm | Reply Hugo Estrada

    Thanks! You are the SQL man!

    
    
14. on January 14, 2009 at 11:40 pm | Reply Nisarg

    Vey helpful dude!! Keep up the good work!
    Jai Hind!

    
    
15. on January 22, 2009 at 8:56 pm | Reply Mark

    Thank you sir! Just what I was looking for!

    
    
16. on February 19, 2009 at 6:11 pm | Reply Andrey

    Thanks. Short and usefull info.

    
    
17. on March 17, 2009 at 1:02 pm | Reply Dickson

    Really useful…

    
    
18. on April 16, 2009 at 8:59 pm | Reply Ramani Sandeep

    i want to store more than 8000 chars in table field..
    i hv used varchar(max) , text(max) ,but i m not able to get success in it..

    everytime data gets truncated whn i insert it..

    so can u tell me how to overcome from this problem..?

    
    
19. on July 6, 2009 at 4:12 am | Reply Chaminda

    I am having the same issue as Ramani. Any solution for this ?
    I have a text field and what ever number of characters that I save on that field, it returns only 1001 characters. But when I checked with DATALENGTH() function it shows that it is having the same number of characters that I entered.

    
    
20. on July 6, 2009 at 11:51 am | Reply Imran Mohammed

    @Chaminda,

    Open SQL Server Management Studio
    Go to Tools – Options (Tools exists at the top left of SSMS)
    Expand Query Results
    Expand SQL Server
    Click on Results to Grid

    On right side, Set Value for :
    Maximum Characters retrieved : Set a higher value.

    Because of this, SQL Server is returning fewer characters. Change this value to a higher value and see the difference

    ~ IM.

    
    
21. on July 20, 2009 at 9:43 pm | Reply Hozher

    Hello!

    But, when the field is null ?
    how can i know the size of a field, have or not have data in the table ??

    Thanks!

    
    
22. on August 12, 2009 at 10:16 pm | Reply Kunal Shah

    Good work !!

    
    
23. on October 2, 2009 at 3:37 pm | Reply Rajesh

    Good tips!!!
    Thanks bro

    
    
24. on October 10, 2009 at 4:50 pm | Reply ran

    can anybody help me in using length function
    how i can make any field have 4 digits (e.g 7456)
    to 6 digits(i.e 007456) in my data base using sql query so that every field with length 4 is changed to length 6
    but zero should come in front

    
    
25. on October 12, 2009 at 10:44 am | Reply ran

    any one there
    plz reply

    
    
26. on October 13, 2009 at 2:29 am | Reply Brian Tkatch

    @ran

    Is this a VARCHAR field?

    UPDATE tab SET col = ‘00′ + col WHERE LEN(col) = 4;

    
    
27. on November 11, 2009 at 4:12 pm | Reply UCMueller

    @ran

    UPDATE tab SET col = RIGHT(‘000000′ + col, 6)

    
    
28. on November 17, 2009 at 8:37 am | Reply Bhargav

    Really great tip.
    Thanks mate.