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
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
working fine. thanks
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