SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A quick and easy way to pull up table names, column names, and specific data structure


A quick and easy way to pull up table names, column names, and specific data structure

Author
Message
jarid.lawson
jarid.lawson
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 436
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

Hemant.R
Hemant.R
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 240
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.
hakim.ali
hakim.ali
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1289 Visits: 1090
... 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
jarid.lawson
jarid.lawson
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 436
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

Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71386 Visits: 40930
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search