Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A quick and easy way to pull up table names, column names, and specific data structure Expand / Collapse
Author
Message
Posted Monday, December 3, 2012 3:33 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:58 PM
Points: 120, Visits: 404
This is another one of my fun little tools that I decided to share. There are times that I need to review the structure of one or (usually) more tables. I need to quickly see all the column names, which are Varchar or Int, and which are Nullable, etc. To do this I made a little tool that makes things go fairly quickly.

Select T.Name As TableName
,C.Name As ColumnName
,ST.Name As DataType
,C.Max_Length
,C.Precision
,C.Scale
,Case
When C.Is_Nullable = 1
Then ''
When C.Is_Nullable = 0
Then 'Not Null'
End As Nullable
,Case
When C.Is_Identity = 1
Then 'Identity Field'
When C.Is_Identity = 0
Then ''
End As Is_Identity
From Sys.Tables T Inner Join Sys.Columns C
On T.Object_ID = C.Object_ID
Inner Join Sys.Types ST
On C.User_Type_ID = ST.User_Type_ID
Where T.Name = '<First Table Name Here>'
----------------------------------------
Union All
----------------------------------------
Select T.Name
,C.Name
,ST.Name
,C.Max_Length
,C.Precision
,C.Scale
,Case
When C.Is_Nullable = 1
Then ''
When C.Is_Nullable = 0
Then 'Not Null'
End
,Case
When C.Is_Identity = 1
Then 'Identity Field'
When C.Is_Identity = 0
Then ''
End
From Sys.Tables T Inner Join Sys.Columns C
On T.Object_ID = C.Object_ID
Inner Join Sys.Types ST
On C.User_Type_ID = ST.User_Type_ID
Where T.Name = '<Second Table Name Here...Copy the entire second query and paste for all additional tables>'

You can add additional dynamic SQL steps so you can pass a basic list of tables via a temp table, and cycle through them. I find that while it is doable, this way is usually faster. If I'm working with > 50 tables then I'll add the temp table approach, but usually I don't need to review that many at once, so this works just fine.

Hope you find this useful.


“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson
Post #1392168
Posted Tuesday, December 4, 2012 12:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
hi ,

you use generate script wizard which is provided in ssms
right cllck on database then tasks you will get generate scripts option.

Using this option u can generate scripts of table and other objects also.
Post #1392269
Posted Wednesday, December 5, 2012 7:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:42 AM
Points: 539, Visits: 809
... or you could simply use the built-in functionality in SSMS. Highlight the table name, and hit ALT+F1. It will give you all kinds of useful table metadata, including:

- Column data types and lengths
- Identity field, seed, increment if present
- Filegroup
- Index names
- Foreign Keys from this table
- Foreign Keys to this table


Hakim Ali
www.sqlzen.com
Post #1393016
Posted Thursday, December 6, 2012 12:49 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 2:58 PM
Points: 120, Visits: 404
True about the other ways to get this kind of data, and I use both of those fairly frequently as well. This is for those times when I have several tables to review, and I just need the basic break down of each table. This is also mainly posted for a quick reference point for me to code I've used before, and use often.

“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson
Post #1393712
Posted Thursday, December 6, 2012 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
jarid you could streamline the use even further if you put that in a stored procedure.

for example, I created a proc sp_GetDDL which returns the table structure of a table, kind of similar to yours.

now, by adding it to SSMS keyboard shortcuts, I can double click on an object name, click CONTROL + 3 and get the results.

so say i'm in the middle of writing a view or a proc, and I need some at-a-glance list of the columns like you suggest...simply highlight the objectname, do the keyboard shortcut, and poof.

something helpful like that increases productivity for me.

the first screenshot is from something similar, sp_find, which just searches tables and columns that partially match some string i highlight.





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1393716
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse