Solved

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

Posted on 2011-09-27
5
287 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

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…
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…

739 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