Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 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