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 (http://blog.SQLAuthority.com)
Yes the nice solution but my request is to find the table list and their columns list
Thank you Pinal Dave
Is there any way to exact similar information from a Microsoft Access table using a Microsoft Access query?
thanks,
Tee
@harinath
select * from information_schema.columns
This view will give you the list of tables and all the columns in those tables with some good information.
Hope this helps,
Imran.
good day mr Imran,
please explain duplicate columns name.
Isa Saleh,
From Nigeria.
I cann’t start the SQL Server 05, anybody knows how to solve this error below?
Thanks!!
Logon failed for login due to trigger execution,
Changed database context to ‘master’,
Changed language setting tu us_english. MSSQL: (error 17892_
Dear Sir,
I want to get column name by sql command. There are 50 columns in my table and how to get column name to variable.
And how to rename column by sql command. I tried to alter table command but it doesn’t work vb program.
regards,
sanjeewa
select column_name from information_schema.columns
where table_name=’your_table’
Read about sp_rename in SQL Server help file to know about how to rename an object
Hi,
Your tricks are nice and simple.
najath
Hi, how do you get the index information of one whole database?
Like, if I want to get all the Index names, column name, column position in index and table name from the database Adventureworks?
I have to compare two similar DB, one as SOURCE and the other as TARGET.
I want to write single query to match the counts of all the individual columns present in SOURCE with that of TARGET.
Can you pls help me out.
Hello Abhishek,
First I wrote a simple query which will return me all the table names with their rows count.
I wrote this query two times, each time using a different database name, and I am then comparing the result of two queries.
Your script starts here :
Things to change in query.
db_name1 – first database name
db_name2 – second database name
This query works only in SQL Server 2005, because Except key word works only in SQL Server 2005.
select A.name, B.rowcnt
from db_name1..sysobjects A, db_name1..sysindexes B
where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id
except
select A.name, B.rowcnt
from db_name2..sysobjects A, db_name2..sysindexes B
where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id
The above query will give you all the tables and rowcount that are present in database1 and are not present in database2.
In order to get all the table names and rowcounts in database2 that are not present in database1 just reverse the query something like this.
select A.name, B.rowcnt
from db_name2..sysobjects A, db_name2..sysindexes B
where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id
except
select A.name, B.rowcnt
from db_name1..sysobjects A, db_name1..sysindexes B
where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id
When I compare tables… I also want to see sometimes what are the common tables present in both the database , at that time I will use something like this,
select A.name, B.rowcnt
from db_name2..sysobjects A, db_name2..sysindexes B
where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id
intersect
select A.name, B.rowcnt
from db_name1..sysobjects A, db_name1..sysindexes B
where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id
All these queries works only in SQL Server 2005.
If you want to execute in SQL Server 2000.
then you have to slightly change queries.. keeping the main part as it is…
select A.name, B.rowcnt
from db_name2..sysobjects A, db_name2..sysindexes B
where type = ‘u’ and B. indid < 2 and object_id(A.name) = B.id
with the use of exists in
let me know if you want a script to run in SQL Server 2000.
Hope this helps.
Thanks,
thx man, helped a lot
thx
I am interested in Angel’s question above. Anyone know how to get column names used in an index, please?
can i get colunm names from the sql statement that I am using……
e.g.
SELECT (tbl_Employee.employee_FName+’ ‘+tbl_Employee.employee_LName) as Author_name, tbl_Forum.Forum_status,tbl_Forum.Forum_id,tbl_Forum.Forum_title,tbl_Forum.Forum_no_of_topics,tbl_Forum.Forum_ModifiedDate,(CASE tbl_Forum.Forum_Approved WHEN ‘Yes’ THEN ‘Approved’ ELSE ‘Not Approved’ END) as ApproveFlag From tbl_Forum LEFT OUTER JOIN tbl_Employee ON tbl_Employee.employee_userid=tbl_Forum.Forum_CreatedBy WHERE tbl_Forum.Forum_Approved=’Yes’
SO I WANT Author_name,Forum_status,Forum_id…..ApproveFlag etc.
in the return statement
Sorry I should have been more specifc, index names, column names for each index, for a table (or tables). The following gives index names (and other info!) for tables:
SELECT i.name AS index_name
, CASE i.type_desc WHEN ‘CLUSTERED’ THEN ‘Yes’ ELSE ‘No’ END AS ‘Clustered’
,is_unique
,ds.type AS filegroup_or_partition_scheme
,ds.name AS filegroup_or_partition_scheme_name
,ignore_dup_key
,is_primary_key
,is_unique_constraint
,fill_factor
,is_padded
,is_disabled
,allow_row_locks
,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id 0
AND i.object_id IN (OBJECT_ID(”), OBJECT_ID(”))
ORDER BY i.object_id;
thank u big man
Mr. Dave,
You are the man! Thank you again!
Thanks boss
Thanks!
Hi All,
I have a problem with my SSIS pkg, I am loading the data from excel sheet into SQL DB, while loading the excel sheet i am getting all null values in between , like i am getting 4 correct rows then couple of null rows, the pattern is continuing , my excel sheet is very cllean , i don understand where the problem lies,
Any help would b appreciated,
Thanks in advance
Prashanth
ADDITINAL DETAILS:
My excel sheet has about 6000 rows, but when i insert it into SQL DB , i goes to 40k with all others being nulls in a random fashion .
hi,
ur aticles are very simple and useful too
Very Helpful
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
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
sir .. i have insert , select ,update ,delete query all r write one store procedure .. i will send table name and field value only …
how to ??? please help me…..
You have to use dynamic sql but it will lead to sql injection. Why do you want to do this?
Sir , Insert Store Procedure … I will send Tablename and field value ….
How to do? Please help me ..
@guna
CREATE TABLE A(A VARCHAR(10));
CREATE PROCEDURE INSERT_INTO(@Table_Name VARCHAR(MAX), @Column_Name VARCHAR(MAX), @Value VARCHAR(MAX))
AS
EXECUTE (‘INSERT INTO ‘ + @Table_Name + ‘(‘ + @Column_Name + ‘) VALUES(”’ + @Value + ”’)');
GO
EXEC INSERT_INTO ‘A’, ‘A’, ‘AAA’;
SELECT * FROM A;
—————-
Note, this is a bad idea for a number of reasons. Please explain why you want to do this so a better idea can be proposed.
Hi All,
I am trying to pull all of the column names from a table in sql 2005 into column ‘A’ of an excel worksheet using vba. I came across this thread and I used the syntax posted here:
strSQL = “Select column_name from information_schema.columns where table_name=’mytblname’”
This works fine, however, when I look at it in my excel worksheet, the first column name is not showing, instead it shows ‘column_name’. The vba code that I am using is below. It pulls the column names in, but then it errors out saying that the item cannot be found in the collection corresponding to the requested name or ordinal. I really would appreciate any helpful suggestions.
strSQL = “Select column_name from information_schema.columns where table_name=’mytblname’”
Set rsmywork = New ADODB.Recordset
rsmywork.Open strSQL, connmywork, adOpenDynamic, adLockReadOnly, -1
For intCount = 1 To rsmywork.Fields.Count
Range(“A2″).Offset(intCount, 1).Value = rsmywork(intCount).OriginalValue
Next
In place of mytablename use the actual table name available
I figured out why it was not showing the first column name. I have fixed my code, but not I am getting another error: item cannot be found in the collection corresponding to the requested name or ordinal.
fixed code -
For intCount = 0 To rsmywork.Fields.Count
Range(“A2″).Offset(intCount, 0).Value = rsmywork.Fields(intCount)
Hello all,
I was querying google to try and find all tables and all their fields with all their data information inside of them and cam accross this web site. Unfortunatly this query was not excatly what I was looking for so here is how to get all information from each field in each table in YOUR database.
select * from [YOUR_DATABASE].information_shcema.columns order by table_name
This is for all who may still be confused just thought I would give out what I found.
Be Well!