Solved

How to use t-sql get table schema and Ordianal Position?

Posted on 2011-09-27
5
277 Views
Last Modified: 2012-05-12
I'm using the following script to get table information. I also like to get table schema (like dbo) and ordianal position. How to get it? Thanks.

DECLARE @tablename varchar(100)
SET @tablename = N'MyTableName'
SELECT
'MyTableName' AS 'TableName',
clmns.name AS [ColumnName],
usrt.name AS [DataType],
'Nullable?'=
CASE clmns.IS_NULLABLE
      when 0 then 'No'
      when 1 then 'Yes'
      else 'Unknow'
END,
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN
clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and
baset.user_type_id = baset.system_type_id
WHERE
(tbl.name=@tablename and SCHEMA_NAME(tbl.schema_id)=N'dbo')
ORDER BY
clmns.column_id ASC
0
Comment
Question by:minglelinch
5 Comments
 
LVL 6

Accepted Solution

by:
dan_mason earned 500 total points
Comment Utility
This function gives you DB Name, Schema, TableName, Column Name, Ordinal position, Datatype, and whether the column is nullable.

I usually use by stating the schema and table name, but because the parameters default to NULLyou can specify nothing and get back all tables:

SELECT * FROM dbo.TableInfo(default,default)

CREATE FUNCTION [dbo].[TableInfo](@schema varchar(20)=NULL, @table varchar(30)=NULL)
RETURNS TABLE 
AS
RETURN
SELECT TABLE_CATALOG AS dbName, TABLE_SCHEMA AS tbSchema, TABLE_NAME as tbName,COLUMN_NAME as colName, ORDINAL_POSITION as colPosition, 
COALESCE(DOMAIN_NAME,DATA_TYPE+COALESCE('('+REPLACE(CAST(CHARACTER_MAXIMUM_LENGTH as varchar),'-1','max')+')','')) AS DataType, 
CAST(CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END as bit) as Nullable
FROM information_schema.columns
WHERE TABLE_NAME LIKE COALESCE(@table,'%')
AND TABLE_SCHEMA LIKE COALESCE(@schema,'%')

Open in new window

0
 
LVL 26

Expert Comment

by:Anurag Thakur
Comment Utility
to get schema use
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility
Hi,

If you are well verse with sql server then debug sp_help procedure.

you will get all you wanted.

To debug

sp_helpText 'sp_help'

Open in new window



- Bhavesh
0
 
LVL 1

Author Comment

by:minglelinch
Comment Utility
Thanks for all replies. Great help.

dan_mason: Your function is really convenient. If also like to get the value of the length for each column, what property should I add? I tried some but not successfully.Thanks.
0
 
LVL 1

Author Closing Comment

by:minglelinch
Comment Utility
All information needed are selected from the function. Thanks. Great answer.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now