how to get table details in single row ?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

Hi ,
Can anyone help me with the query to get table name,column name , column datatype , isprimary etc in single row .

for eg:
My output of employee table with Columns E_Id,E_Name,E_City should be as below

--------------------------------------------------------------------------------------------------
TabName ColumnName datatype isprimary Columnname datatype isprimary Columnname datatype iprimary

Employee - E_Id - int - S - E_Name - varchar - N - E_City - varchar - N

--------------------------------------------------------------------------------------------------

Posted 7-Feb-14 0:26am

MurugappanCTS

Updated 7-Feb-14 0:28am

v3

Add a Solution

2 solutions

Solution 2

chage your table name and column name

SQL

select t1.user_id,t1.DATA_TYPE,t1.IS_NULLABLE,t2.name,t2.DATA_TYPE,t2.IS_NULLABLE,t3.city,t3.DATA_TYPE,t3.IS_NULLABLE from
(select  a.user_id ,b.DATA_TYPE,b.IS_NULLABLE from  tbl_user_registration as a left join INFORMATION_SCHEMA.COLUMNS as b  on  b.TABLE_NAME='tbl_user_registration' and b.COLUMN_NAME='user_id')as t1,

(select  a.user_id,a.name ,b.DATA_TYPE,b.IS_NULLABLE from  tbl_user_registration as a left join INFORMATION_SCHEMA.COLUMNS as b  on  b.TABLE_NAME='tbl_user_registration' and b.COLUMN_NAME='name') as t2,

(select a.user_id, a.city ,b.DATA_TYPE,b.IS_NULLABLE from  tbl_user_registration as a left join INFORMATION_SCHEMA.COLUMNS as b  on  b.TABLE_NAME='tbl_user_registration' and b.COLUMN_NAME='city') as t3  where t1.user_id=t2.user_id and t1.user_id=t3.user_id

you may get ideas

Posted 7-Feb-14 0:44am

King Fisher

Updated 7-Feb-14 0:49am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ahmed Bensaid · Accepted Answer · 2014-02-07T00:35:00

Solution 1

Take a look at this thread's accepted answer ;)
http://stackoverflow.com/questions/10784369/to-get-table-details[^]

Posted 7-Feb-14 0:35am

Ahmed Bensaid

Comments

MurugappanCTS 7-Feb-14 6:42am

this is not in my expected form . I expect one row for a single table detail not in multiple rows ??

Ahmed Bensaid 7-Feb-14 8:25am

In my answer you have the solution. You just have to adapt it a little. CodeProject is not there to do your work for you ...

MurugappanCTS 7-Feb-14 8:35am

Boss Already i have taken the solution which i want with the help of above example . but i am unable to use pivot.to turn into a single row

Ahmed Bensaid 7-Feb-14 9:27am

http://stackoverflow.com/questions/17195709/display-multiple-rows-and-column-values-into-a-single-row-multiple-column-value