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

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
LVL 1
minglelinchAsked:
Who is Participating?
 
dan_masonCommented:
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
 
Anurag ThakurTechnical ManagerCommented:
to get schema use
SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

0
 
Bhavesh ShahLead AnalysistCommented:
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
 
minglelinchAuthor Commented:
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
 
minglelinchAuthor Commented:
All information needed are selected from the function. Thanks. Great answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.