Solved

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

Posted on 2011-09-27
5
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 6

Accepted Solution

by:
dan_mason earned 500 total points
ID: 36713775
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
ID: 36714432
to get schema use
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36714836
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
ID: 36717843
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
ID: 36718032
All information needed are selected from the function. Thanks. Great answer.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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