In today’s article we will see question of one of reader Mohan and answer from expert Imran Mohammed. Imran thank you for answering question of Mohan.
Question of Mohan:
hi all,
how can i get field name and type etc. in MS-SQL server 2005. is there any query available???
Answer from Imran Mohammed:
@mohan
use database_name
Sp_help table_name
This stored procedure gives all the details of column, their types, any indexes, any constraints, any identity columns and some good information for that particular table.
Second method:
select column_name ‘Column Name’, data_type ‘Data Type’, character_maximum_length ‘Maximum Length’ from information_schema.columns where table_name = ‘table_name’
Hope this helps,
Imran.
Let see Imran’s example using sample database AdventureWorks.
USE AdventureWorks
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length'
FROM information_schema.columns
WHERE table_name = 'Address'
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
80 Comments. Leave new
In reference to Imran Mohammed example:
USE AdventureWorks
GO
SELECT column_name ‘Column Name’,
data_type ‘Data Type’,
CHARacter_maximum_length ‘Maximum Length’
FROM information_schema.columns
WHERE table_name = ‘Address’
Can you do the same thing using a temp table like #test?
select * from tempdb.sys.columns where object_id =
object_id(‘tempdb..#mytemptable’);
sir,
i want details of a table such as column name,data type,primary key,foreign key ,and foreign key column name
i want this for documentation of college project
in college it is called data dictionary
please reply
EXEC sp_help ‘table_name’
would do what you want
hello,
I want to know a sql query!
there is table name student contain columns like name, standard, roll, percentage.
i want to see those column records only, in which the column name contains like ‘o’
eg, here column name roll contains character ;o;
so roll number column and its respective records to be shown .
select * from student where roll like ‘%;o%’
Hi,
You are the answer to my prayers! Thanks for the query!
Best Regards,
Clarence D.
Hi,
how to parse the column name as a value that can be compared.
Example:
create table Test
(columnname varchar(255))
insert into Test
values(zipcode)
parameters: @InTable1 nvarchar(255),
@InCol1 nvarchar(255)
I have to join the tables by @InCol1 = test.columnname
Dear Sir,
Is there any way to add two clustered indexes to a table? I know there isn’t, but how would you imitate the same working
then. Please suggest.
Wow! that’s a great situation and really tough to answer. Best of luck man and me too waiting for the reply !!
Hello Ramlal,
Can you explain what you want to acheive for that you need to imitate two clustered indexes on a table. If you can describe your requirements than we can provide a better solution.
Regards
Pinal Dave
Thanks for you concern Sir, but I myself don’t know the exact requirement. Actually I was been asked the same by my senior DBA and I thought you could help me.
Anyways its ok and thanks again .
Tell your senior DBA that it is impossible. Clustered index defines how the actual data sorted. You can’t store data sorted in multiple ways obviously (you can fetch it ordered any way you like, of course). Unless you use some perverse scheme where you duplicate the whole table.
MSDN conforms this: There can only be one clustered index per table, because the data rows themselves can only be sorted in one order
Very nice and thank u
hello,
I want to know a sql query!
there is table name SystemParm contain columns like SystemParmID, ParmLabel, ParmTypeCode, OrderNumber,etc.
i want to select only particular ColumNames from SystemParm table
eg, here ColumnNames SystemParmID,ParmTypeCode
i want select these ColumnNames
i want to fetch columns name but one by one in a loop which traverse from first column to last column for one particular row. please help me out…..
Thanks
Niket
I want to search the columnsname but one by one in the asp.net
I want to search the data .why get one data search and disply the data in the gridview.type a particular latter.to search the table.
Sir plzz give me answer of my question……….
Q:- I have table name “EMPLOYEE_LEAVE_RECORD”.This table have only 2 column name “ID” and “LEAVE_SESSION” and “ID” is int and “LEAVE_SESSION” is char(1) and table have only 2 record “ID” is 1 & 2 and “LEAVE_SESSION” is A & J. So my prblm is
How can find “LEAVE_SESSION” value
A as a April to March &
J as s January to December by any query????????……….
Plzzz….sir….i’ll wait your answer………….
Q)may i modify user defind data type in sql server 2005.
example:
i created user defined datatype ‘std_name’ with varchar(30).
it was used by diffrent tables.but now i want to modify my
udt from varchar(30) to varchar(50).is it possible ………
I don’t know for Microsoft access 2007.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”).newInstance();
con = DriverManager.getConnection(“jdbc:odbc:;DRIVER=Microsoft Access Driver (*.mdb);DBQ=D:\\lab5.mdb”,””,””);
SELECT column_name,data_type,
Character_maximum_length
FROM xxxx
WHERE table_name = ‘Address’;
xxxx = ?
Yes the nice solution
Thank you Pinal Dave
Hi Pinal.
Main Query is Can we get running totals for A Pivoted result.
My scenario is like this.:
I need to declare a dynamic table ,Let us say “tmp_Result” with dynamic number of columns
Which will hold the data returned from
a select statement(This is a pivoted result).
For this i need to get the [SubDept1] datatype,[SubDept2] datatype,[SubDept3] datatype as string so that I can Create the table.
And Then i need to insert a row with Totals for each column in the temp table
The sample select statement as below:
Select * from (
select distinct City,SubDeptName,VAlue from tbl_Cities cl
inner join
[RelationTableWith Value Column] b
on(cl.cityid=b.cityid)
inner join tbl_SubDept g on(g.subdeptid=b.SubDeptId)
)t PIVOT
(max(Value) For Value in([SubDept1],[SubDept1],[SubDept1]))as pivottable
hi all.,
can we assign the Font-Names to table Column in sqlserver2005 please help
LIke Arial,TLB-TTHemalatha,TimesNewRoman etc.,
how we can get column name and corresponding column value(Row data) from the Table?
Can u help me sir?
This is what you want. Try the method suggested here