A quick and easy way to pull up table names, column names, and specific data structure
-
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.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein"DOH!"
- Homer Simpson[/font] -
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.
-
... 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 -
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.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein"DOH!"
- Homer Simpson[/font] -
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!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply