USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
Reference: Pinal Dave (http://www.SQLAuthority.com)
how to find number of columns in a table in sql server 2000.
Hi Vikram,
Above script will do the same for you. Col_Count Variable will give you the number of columns in table.
Regards,
Pinal Dave
(http://www.sqlauthority.com)
I want to generate script of a database by just providing the database name.how will i do that
Hi
I need to find a row to given value then read next value of row
Example
Row Width
1 400
2 500
3 510
4 682
So I am find value 500 but I want display next row value 510
How to find the next row value in Sql
Please reply me , I am waiting for your mail..
Regards
Selva
select width from table
where row=(select min(row) from table where width>500)
HI ,
How can we get Row count for each column of a given table.
where if the row count should say ,
if Nulls are present in entire column for all rows should dispaly – NULL ,
if the entire column is Blank for all rows it should show BLANK.
Thanks.
How would I get column names added along with this query for each table?
hello,
what should we have to write query for counting a number of rows in a table. submit query.
How can we find out the Column names in SQL Server
like the ORACLE command
DESC
which is use to show the table structure
thanks
how can we find the number of tables starting with some’p'in a database
select table_name from information_schema.tables
where table_name like 'p%'
This works great!
to show the how many records in one database
How can we get the row number of a particular table ?
By default there is no row_number
However based on the order of the column you can find it using row_number() function from version 2005 onwards
select row_number() over(order by somecol) ,* from your_table
This SQL Query returns total numbers of column in table
SELECT TOP 100 PERCENT WITH TIES c.TABLE_NAME , COUNT(*) NumColumns
FROM INFORMATION_SCHEMA.[COLUMNS ] c
inner join INFORMATION_SCHEMA.[TABLES] t
ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
and c.TABLE_NAME = T.TABLE_NAME
GROUP BY c.TABLE_NAME
ORDER BY c.TABLE_NAME
You dont need top 100 percent as there is no subquery used
Hi Champs ….
Try this
SELECT COUNT(*) Statment will return the row counts !!
and one more thing with using SELECT COUNT(*) statment ….
Because the SELECT COUNT(*) statement makes a full table scan to return the total table’s row count, it can take an extremely long time for large tables. There is another way to determine the total row count in a table. In this case, you can use the sysindexes system table. There is a ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2
Cheers
Praveen
Note that if you use version 200, you need to use
dbb updateusage(‘db_name’)
in order to have correct number of rows
Thanks Pinal you just saved my life :)
Write a Query to select column value in row form
Like
Table
Col1 Col2
Aaa 1
Bbb 2
Ccc 3
Ddd 4
Eee 5
Out put of query should belike
Col1 Aaa Bbb Ccc Ddd Eee
Col2 1 2 3 4 5
Search for PIVOT in google
hi vinod,
try creating a udf. add a cursor to read each row in your table and concat the values as you read. output the values using the udf a select statement.
hope this helps.
No. That is not the effecient solution
From the output it seems that he wants PIVOT
select COUNT(*) from dba_tab_columns
where table_name = YourTableNameHere;
Table name should be in upper case!!
It is ok if the database is case insensitive
Thanks a lot
How to get the no. of rows in a table structure ?
hi suganya,
you can write the query in this way
select count(any column name) from yourtable.bcoz u can take any column to find the no of rows
That is not very true
If you use *, you will get all rows count
If you use count(col), you get count where col is not null
Select Count(*) from Table_Name is the query to get total no of rows in a table … we cannot take Column_Name in palce of * bcoz
Count(Column_Name) gives count of that particular column whereas Count(*) gives count count of entire table Rows
To be clear, count(col) will omit NULL values
Thanks for this info. Basic knowledge that i dint have :-)
SQL Server 2000 & 2005 both support a stored procedure call sp_help
call it with the tablename following it and you’ll get your table structure and indexes, filegroups, entities, etc….
here are some examples of how to use it…
sp_help MyTableName
sp_msforeachtable “sp_help ‘?’”
sp_msforeachtable “select ‘?’ TableName, count(*) as TotalRecords from ?”
@radhika, @MarownIOM,
Your comments have added value to this blog and community.
Thank you,
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Thanks pinal
hi guys,
how can i show some particular columns from a tabel without writing any column name?means even dnt know abt that particular column name.
bhuvnesh
You can try the following link
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/select-data-from-top-n-columns.aspx
The idea is just give the number, it would give data to those many columns
If you speficy 10, you will get data fro first 10 columns
hi
I saw your blog
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/select-data-from-top-n-columns.aspx
But i am still getting getting a problem
I have a table Xho.Department where
And Xho is for schema
now when i use below query
Select * from information_schema.columns
where table_name =’Xho.Department’
it returns nothing
But I am Having A table Name ‘Xho.Department’
so when i pass the table name ‘Xho.Department’ ,4 in your sp it produsing an error ……………..
Msg 536, Level 16, State 5, Procedure TopNcolumns, Line 26
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘from’.
What happens you when try this?
Select * from information_schema.columns
where table_name =’Department’ and table_schema=’Xho’
How can we find out the Column names in SQL Server
like the ORACLE command
DESC
which is use to show the table structure
thanks
—
To find a column name in a table..
Use ”
Go
Select Column_Name from Information_Schema.Columns
where Table_Name = ‘
Note ** you can also use a wildcard
Hopes this helps
Wole
dIRECT EQUIVALENT IS
SP_HELP 'TABLE_NAME'
how to list the columns of a particular table when table name is given as input??
There are many methods
Some of them are
EXEC sp_columns ‘table name’
SELECT column_name FROM information_schema.columns
WHERE table_name=’tabl name’
EXEC sp_help ‘table name’
Thanks alot for your script. It can be very useful.
Lalit
How to count indexes in particular table. i.e if we have 3 indexes in a particular table then how could we count in query. pls help
hey very nice! thanks Pinal! I am new to SQL and this came in very handy!!
HI Wole,
Thanks for your query finding column names in a tabel using Information_Schema.Columns…
i’m new to sql server…
Thanks & Regds
Rockin Benjamin.S
MS SQL query of showing all tables whose name starts with “P” in specefic database, use this query.
select * from sysobjects where name like ‘P%’
But It will show Constraints and tables,
if you want only user defined tables then use this query.
select * from sysobjects where name like ‘P%’ and xtype = ‘u’
Hi guys,
This is another easy way to find the max row count in a table.
select rowcnt from sysindexes where name in (
select name from sysobjects where parent_obj in
(
select id from sysobjects where name = ‘Table_Name’
)
hi wole
Thanks for helping in counting the number of columns in a table
thanks again
plz anyone send the query for how to count the number of columns in a table
How can we write to query for percentage of the marks?
Hi dave, Thanx for the info. I’m stuck with getting roWID from a select statement. I can’t use ROW_NUMBER() because i’m using 2000 instead or 2005
select count(*) as ‘Total Rows’ from tablename
this query will count number of rows in a table
Find the total rows in Table without the count(*)?
There are many methods like usage of SUM(1), COUNT(1) etc
Refer this for more methods
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx
Madhivanan
how can i count number of column in a row with same data?
i have a table with column empid,month, day1, day2 ………….day31 i want to count number of ‘p’ and ‘a’ of that employee id?
select empid,count(*) as counting from employeetable
where empid in ('p','a')
group by empid
i think you did not get the scenario correctly, here the requirement is something like an attendance sheet, where an empid is marked present (p) or absent (a) based on the days and @Deepshika want the number of days present in that month.
take a table, table name : Attendance
_____________________________________________
| EmpID | Month | Day1| Day2| Day3|………………..|Day31|
————————————————————————
|001 | Jan | P | A | P |………………..| P |
|001 | Feb | A | P | p |……………….| |
|002 | Sep | P | P | P |………………..| |
|002 | Nov | A | A | P |………………..| |
|_____________________________________________
and so on,
so as per @Deepshika requirements is to get the count of no of days present (P) or absent (A) for the empid 001 in the month of Jan and same for all the months and empid’s. here we need to use UNPIVOT, to the count of columns value.
select EMPID, MONTH, DATA, Count(Data)
(select EMPID, MONTH, COL, DATA from
(select EMPID, MONTH, Day1, Day2, Day3,……………,Day31 from Attendance) as pvt UNPIVOT
(DATA for COL in (Day1, Day2, Day3,……………,Day31) as UPVT)) as pt group by EMPID, MONTH, DATA
i have given the sln i know, there may be many slns better than this one. if anyone know a better sln than provide that one here which will be helpful for other for developing the skills.
Column count in a table
create Procedure Prc_GetColumnCount
@TableName varchar(20)
as
Begin
Select Count(Name) from Syscolumns
Where
id =
(Select id from SysObjects Where name = @TableName)
End
Prc_GetColumnCount ‘Table name’
How do we extract all table list from SQL server?
I am using toad for SQL server and trying to find some field.
Can anyone pls tell me how to see the list of all the tables in SQL Server…………………
One of the methods is
EXEC sp_tables
Hey Awadh ….Here’s ur Answer..
select TABLE_NAME from INFORMATION_SCHEMA.[TABLES]
What is the maximum number of records that SQL will return for a query? e.g. I have a db of 15,000 records and I want to run a query which should return 13,000. Will i see all 13,000 or some fewer number?
Thanks
You will see all rows. But if you want to return billions of rows, it depends on the server’s RAM capacity
Hi Friends
You can refer
http://www.mssqlcity.com/Articles/KnowHow/RowCount.htm
SELECT rows FROM sysindexes WHERE id = OBJECT_ID
(‘TableName’) AND indid < 2
Thanks
Hi Frnds
You can get list of all user defined table and no of rows from your sql server database with this query.
It is quiet efficient and optimized.
SELECT Rows, object_Name(id) as TableName FROM sysindexes WHERE indid < 2 and OBJECTPROPERTY(id, ‘IsUserTable’) = 1
Cheers
abhi :)
Note that for versions prior to 2005, you need to use DBCC updateusage for correct counts
Also refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx
Hi,
Really this is nice query. Thanks…… i am very happy
Hi,
In T SQL, how can we automate:
- to copy all the columns of a table to make a new table
- change the names of the new table and its columns using something like a prefix, which will make the difference of structures between the two tables.
Thanks
I guess u r looking something for this
Use AdventureWorks;
Select EmployeeID as ‘EmpNo’, LoginId as ‘LoginName’, BirthDate as ‘DOB’
into MyNewTableName
from HumanResources.Employee;
Go;
It creates new table and populates result set in that table
@ Bimpa ,
You can do two things, based on what you are looking at.
1. If you just want a new table structure with out any constraints, keys, indexes…. just the columns and the table thats it, then try using…
select * into new_table_name from old_table_name
This will create the table and at the same time it will copy all data from source table to destination table, if you are using other database for destination table then use the complete notation, like servername.databasename.owner.object name.
you can schedule this tsql as batch…
2. You can try DTS or SSIS package to do the same. It will create a table on the destination, only table no constraints, no indexes … nothing only table with all columns, data types remaining same in both the cases.
This task also creates a table and then inserts all the data from source to destination. you can schedule this package.
3. if you want to have an exact table in the destination with all the features of source table, then you might want to work like this,
create a batch file which will have all these things,
1. script the source table and then run the script on destination, where you want to create the table, this will create a table with the same name with all the features of the source table.
2. if you want to change the name, write another query,
sp_rename object ‘oldname’ , ‘newname’
3. Then final step, insert all the values from source to destination.
insert into new_table_name select * from old_table_name.
and schedule it … I hope it will work… I am not sure !
1. How to find out the table given a column, you only know that there is a table which has this column name, based on this information you can find out what table is it.
use database name
select table_name from information_schema.column where column_name = ‘column name’
2. If you know the table name and you want to know what is the name of the column, there are many ways I am sure, one amoung them is,
sp_help table_name … it will give alot of information… like owner of the table, all column name, their data type, primary keys , foriegn keys, indexes, constraint …. very useful information.
3. You dont know what was the name of the table and what was the name of the column and you still want to search table and column,
use database_name
select table_name , column_name from information_schema.column — copy this code as it is, dont try to change the table_name or column _name
How to find out user defined table names in the database,
use database_name
select * from sysobjects where type = ‘U’
Similarly you can find out other objects created by user, simple change type =
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = In-lined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table ( this is the one I discussed above in the example)
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Hi Pinal,
Please answer my questions?
1. When a database is restored to the original drive with the original filename, is the dbid changed?
2. When a database is restored to a different drive with the original filename, is the dbid changed?
Mani
Hi Pinal,
Here is the scenario:
On Saturday morning, I created a login named abcd using Enterprise Manager (EM).
In the database access tab, I gave abcd login access to databases x, y and z. I gave it db_datareader and db_datawriter roles in all three databases.
When I looked in the databases, I saw abcd user had been created in each database with all the correct privileges.
When I refreshed logins in EM, login abcd did not show that it had access to database z, but still had access to databases x and y.
None of this makes any sense. EM knew the correct database when I created the login and granted access, but did not show it after I refreshed.
When I did the same scenario on Monday morning, it worked properly and EM showed the correct database access.
I want to know what happens?why not in sat but in Monday?
Does SQL Server actually drop the database
when the database is restored?
Regards,
Mani
Each time I google advanced stuff for sql server I keep coming back here ; )
Thanks , thanks , thanks !!!
No one small idea partly made possible by this blog:
Use the description field to pass info for the GUI generator and get rid of all hardcoded names in GUI ; )
SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], value AS
[DESCRIPTION]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
= c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
c.name = sc.column_name
WHERE class = 1 and t.name = ‘TableName_tb’
Could somebody tell me how to regex the [DESCRIPTION] = e.g. return only set of this one mathing the regex ?
some of the tables have 0 rows in my database. I want to run a query to list out all the tables with 0 rows. how do i do it?
i tried * from user_tables where num_rows is null
but this query retrieves wrong data.
Can anyone gimme the right query?
Use the method
select rows from sysindexes where object_name(id)=’table_name’ and indid<2
Also make sure you read this post
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx
Hi
I want to find the perticular number from the column like
ex —11658458784
21565454797
21564567897
now i have to take 6 digit from the all number .
One method is
where len(col)=6
Hi Dave,
please reply back to my questions
a. How to get rows of a column with out the column name.
Thanks in anticipation
bye.
How to get the table structure in mssql similar to “DESCRIBE tablename” in MySQL.
EXEC sp_help table_name
how many tuples we can inserted in a relation
I have got here answer of my question that is “How can i count number of columns in a table”. Thanks Pinal.
HI,
I want to know the table structure using the quiery.
Plz send me the quiery to know abt the table structure
how can i retrive the number of columns in a table is not satisfied for me give me brief introduction
I found an alternative for ‘DESCRIBE tablename’ in mssql
“SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’myTable’;”
Actually similar equivalent is
EXEC sp_help ‘mytable’
I just want to read a specific row from a multi row return table…….i mean suppose……a query return this
date
——-
11/1/2007 3:19:28 PM
12/1/2007 3:19:28 PM
3/1/2008 3:19:28 PM
2/1/2008 3:19:28 PM
now…..how can i get an a specific row from above…..suppose i want to read each row but through numeric no…….if i say row no.3 or row no.2………..what should be the query……if anybody know plz help…….voidbutreturn@yahoo.com
hi dave
how to count the no of columns in a table…
If I have a column whose every individual row value is known,
how (by mssql query) can I calculate the row number of one particular value in that column?
That is , what is row number of a value in a column?
eg, a column ‘animal’ consists of 5 row values. cat,dog,man,bird,fly.
the value ‘man’ belongs to 3rd row. But if I know only the value ‘man’ how can I calculate the corresponding row number , which is 3? (only by MSSQL query,using access 2003, and visual studio 2005)
Hi,
This is mustafa.
I wrote one stored procedure.
Inside the stored procedure I wrote one query it return mulitple set of record I want add all values that mean total what function I have use.
Is this?
select sum(col) from table
I need row count for all the tables in a database that utilizes schemas.
Hi,
Please bear with me as i am a newbie
Is there any way to find out the no of rows for each month in a year for the last 10 years. so i should effectively get 120 rows (12months X10 years). I am able to create a stored procedure to retrive data based on month and year but cant i get this information at one shot?
This is what i could come up with
create procedure usp_emp_birth @month varchar(10),@year varchar(10)
as
(
select @month as month ,@year as year, count(*)as [no of rows] from HumanResources.Employee
where datename(month,birthdate)= @month and datepart(year,birthdate)= @year
)
exec usp_emp_birth may,1970
how to find number and different tables in perticular schema
select table_name from information_schema.tables
where table_schema='your_schema'
my result set is:
Name data
abc new
sss old
abc current
xxx jadu
Now i requered show on follwing formate:
abc new
sss old
current
xxx jadu
pls give me solution?
It is called Suppress if duplicated
If you use front end application, do it there
How to get table list(table names) for a database according to batch size.
e.g. if i there r 50 tables in database then if i want table names from 10 to 30 range.
reqired in sql 2000 and oracle
Search for Pagination in google/bing
Hi, Dear
Thank for co-operation to me
by your website
given the number of rows and columns how do we find the degree of Table?
For example: What’s the degree of table having 10 cols and 1000 rows
How to find ,how many columns in a table?
select count(*) from information_schema.columns
where table_name='your_table'
Its really good site
Hi,
I have a table like:
Table1
(
col1,
col2,
col3,
)
It has following sample data:
col1 col2 col3
1 2 3
I want to run a query which provides the following output:
COL1 1
COL2 2
COL3 3
Can you please show me how to do this. I am using SQL Server 2005.
Thanks,
Amit
select 'col1' as col, col1 as value from table
union all
select 'col2' as col, col2 as value from table
union all
select 'col3' as col, col3 as value from table
Dear Pinal,
sp_spaceused will never give you updated row count of large table you have to use updateusage before this.
From version 2005 onwards, it is automatically updated and no need to use updateusage
Hi,
Nice info, but i have started learning SQL now. Could you pls give some tips on how to go about this. I am a tester with 4+years of experience.
Thanks
Sachin
Good.
you can switch to oracle.
Hi Amit
Same you can achieve with PIVOT, you can refer pinal’s very nice artical on this (http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/)
Hope this would help.
Cheers :)
Abhishek Doyle
Mumbai
Hi all,
How to print the column name and that values in a row? I need to use that in one select query.
(i.e)
Dep.Code No.Of.Staff
——————————–
101 25
103 40
Total 65
Thanks,
Karthik…:-)
select dept_code, count(*) as no_of_staff from your_table
group by dept_code
Hi all,
I have a stored procedure which is used for insert/update of records in a table. A log is maintained for each operation performed by the user, in the application, and i need to insert the record into the log table only if successful transaction of insert/update is committed.
For the log table, i need the column name of the row which is been updated in the query executed above.
Please tell me how to get the column name which is been updated by the “Update” statement?
Thanks in advance.
Great job. Exactly what I was looking for.
To find out no of columns in a table
select COUNT(*) from information_schema.columns
where table_name = ‘Your_Table_Name
how to find number of columns in a table without using count(*) ?
Reply soon.
Vanita
Hi,
How to return column names where the column contains a particular value. This will always be a single row – each column will contain either 1 or 0. I want to only return the columns that contain the 0 value.
many thanks
Why do these queries on the same table yield different results:
–This statement runs very fast
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘mytable’) AND indid < 2
–This one takes a while to execute
select count(*) from mytable
Mytable contains over 3millions records.
If you use versions prior to 2005, you need to run
dbcc updateusage command to get correct count
I am writing a join. The join is producing more records than desired.
Is there any way I can see the number of columns of the query returned when I say ‘select * from…’
Number of columns of the query that was just executed, not of some table that is already there.
Not possible until you move resultset to a temporary table
Nice Link
Hi All,
How to find the particular row in table?(means suppose one table contain 10 rows , in these 10 rows how to find the particular row(example in 10 rows i want 5 row how)?
There is no easy way to identify the row number until you order it by a specific column. You can use row_number() function Refer this link
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
hi……
select COUNT(*) from information_schema.columns
where table_name = ‘Your_Table_Name
its not working in oracle…………
You need to use ORACLE’s system tables
Do the google for the same
Hi, Ravi you can use following code to get the list of tables having 0 records.
———————————
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp EXEC sp_msforeachtable ‘sp_spaceused ”?”’
SELECT a.table_name, a.row_count, COUNT(*) AS col_count, a.data_size
FROM #temp a INNER JOIN information_schema.columns b ON a.table_name
collate database_default= b.table_name collate database_default
WHERE a.row_count=0
GROUP BY a.table_name, a.row_count, a.data_size
DROP TABLE #temp
—————————-
Hi Dave,
I had a requirement like, I want to show the no of rows(count) as column name in temp table.
Thanks,
Sathish.
Hi Sathish,
if your are using SQL SERVER 2005, then you can use this following syntax
SELECT *,
COUNT(*) OVER(PARTITION BY ””) AS TotalRows
FROM TbaleName
Thanks,
Tejas
You dont need a partition
SELECT *,
COUNT(*) OVER() AS TotalRows
FROM TbaleName
Hi
Pinal
Thank you Very Much
Your code to insert multiple rows with one sql statement was useful…
is there any query to get the column count from a table using sql command
Thanks Tejas Shah.
Thanks! This is exactly what I needed!
hi guys,
i don’t usually use sql in my job…. but for this one purpose i’ve to use sqlite…
so can somebody just tell me a simple query to count just the number of columns in a table…
please hurry.. my time is running out…
PS. this sql is not that bad as it seems :)
@Sid
There are many ways to get this done, here are some of these.
select Count(*) ‘No Of columns’ from syscolumns where id = object_id(‘example’)
or
select count(*) ‘No Of columns’ from information_schema.columns where table_name = ‘example’
Or
select Count(*) ‘No Of columns’ from sys.columns where [object_id] = object_id(‘example’)
Hope this helps,
IM.
hi all
thanks for ur comments….
Hi Guys
New to SQL coding so can someone please help? I monitoring our replication and would like to take a row count of a table ‘table1’ and match it to the replication table on our tier 2 and 3 environment .
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
But I don’t want to receive and email everytime indication everything is ok. Ideally I would like to receive and email\ alert when the row counts do not match.
The one table I am looking at sits on 5 different servers. Sorry bit stuck !!
Also If I wanted to do row counts for mutiple tables accross the servers i.e table1 on all five servers, table 2, table 3 etc.
How could like be done?
Thanks in advance for your help!!
thx Pinal :) It helps me today to detect that tbXml table in Wsus 3.0 with 160.000 records has 1GB size :OOO
Makers of WSUS are crazy ;)))
Thx anyway for simple and handy snippet.
Regards
Thanks!! great tip saved me a lot of time
Hi Pinal,
Consider there are 5 databases in a sql server.Each database contains n number of tables. A column say ‘EMP id’ is repeated in different tables across the databases.
My requirement is to write a single query which should return the count of that column in all the databases.
Can u help me with this
Thanks !! It’s greate……………
Hi All,
Guys i need to create a reports in SSRs which gives me the comparsion on rowcount of table between 2 different database.
2 different database has the same number of tables and with the same name.
How can i get the rowcount of all the tables in both the database.
Thank in advance
Lalitha
Hi Laitha,
You can write Procedure to get the number of rows from Different Database in same SQL server instance.
For procedure please go : http://www.sqlyoga.com/2009/04/sql-server-query-to-compare-number-rows.html
Thanks,
Tejas
salam
if any one help me in topic how we join the different column in same tabel
How would you do a query that returns the list of all of the column names, data types of each column and max size of each column (ie. varchar(50), varchar(100), etc…)
Been stuck on this with no success ….
Thank you !
exec sp_help 'table_name'
Never mind … you already posted this …. of course :)
http://blog.sqlauthority.com/2008/08/05/sql-server-2005-get-field-name-and-type-of-database-table/
SELECT crime.fpo,crime.cdd,crime.cdn,crime.cfn,crime.filedate
get_field(crime.fpo,crime.cdd,crime.cdn,crime.cfn,crime.filedate) col
FROM (SELECT DISTINCT fpo,cdd,cdn,cfn,filedate
FROM crime) crime where id=’MARSCARLCR952059′
this query doesnt return the output,,plz correct it
SELECT DISTINCT O.Name, I.Rows
FROM SysObjects O WITH (NOLOCK) JOIN SysIndexes I WITH (NOLOCK) ON (O.ID = I.ID)
WHERE O.xType = ‘U’
AND I.Rows = (SELECT MAX(II.Rows)
FROM SysObjects OO WITH (NOLOCK) JOIN SysIndexes II WITH (NOLOCK) ON (OO.ID = II.ID)
WHERE OO.xType = ‘U’)
Hi,
I’m a novice to sql scripting. Am trying to figure out a design problem, involving some arithmatic computation.
I have two tables temp1 and temp2 with two feilds account no., ( Common in both tables ) balance ( float data type ).
1. I want to compare the balance feilds in temp1 and temp2. print out the no. of accounts and percentage of match and mismatch.
2. output the a/c nos. whose balances dont match into a seperate table..
another question, that I have is, how does sql handle the computation, if a value in a particular field is divided by 0 ?
any help, will get me started.
thanks
Nice Article:
>> Garima Wrote
hi……
select COUNT(*) from information_schema.columns
where table_name = ‘Your_Table_Name
its not working in oracle…………
>> Reply to Garima
I think dba_tab_columns will help you in achieving what you need.
Assuming you are using 10g,
sqlplus / as sysdba
select count(*) from dba_tab_columns where table_name= ‘YOUR_TABLE_NAME’;
For example:
SQL> show user;
USER is “SYS”
SQL> select count(*) from dba_tab_columns where table_name=’EMP’ and owner=’SCOTT’;
COUNT(*)
———-
8
SQL> select column_name from dba_tab_columns where table_name=’EMP’ and owner=’SCOTT’;
COLUMN_NAME
——————————
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
8 rows selected.
SQL>
Hi,
Can any one help me in writing an SQL Query,
in a database I having number of tables and out of them I want to have count of the tables that has ‘empno’ as one of their column.
Please reply.
Thanks & Regards,
@Rao
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ‘empno’;
Guys ..
Used the below query …
Sp_spaceused
What if I have tables that are the same name but just different schemas. For instance in the database there was schemaA.TableA and schemaB.TableA. So TableA is listed two times in the same database but under different schemas. The spaceused for that query at the top wont work. Is there another query that will get all the tables and schemas space used?
dear sir pinaldave.
i have one doubt i have created a table dynamically using my .net project and it has not primary key what i did is i have altered the table i add a IndexID using
alter table dbo._COMPANYDETAILS ADD IndexID int;
this query
but now what i need is the table has 5000 records i have to number it in the IndexID column from 1 to 5000 what is the query for it please reply me as soon as possible to my mail(ksnavinkumar@live.com) please sir…
ALTER TABLE dbo._COMPANYDETAILS
ADD IndexID int Identity (1,1)
@NavinKumar
ALTER TABLE dbo._COMPANYDETAILS
ADD IndexID int Identity
Thats it. this should do the work.
~ IM.
Hi,
Thanks for such nice query.
It is very nice idea to check entire database.
I find the question from OW very relevant and important. Matching tables by their names duplicate the records from tables with same names in different schemas. Can anybody please shed a light to this point?
Here is my solution using catalog views, which are more flexible than using stored procedures, and considers tables with same name in different schemas:
USE my_DB
SELECT s.[name] [schema]
, t.[name] [table]
, t.create_date
, t.modify_date
, p.rows
, SUM(a.used_pages) * 8 data_KB
FROM sys.schemas s
JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]
JOIN sys.partitions p
ON t.[object_id] = p.[object_id]
JOIN sys.allocation_units a
ON a.container_id =p.partition_id
GROUP BY
s.[name]
, t.[name]
, t.create_date
, t.modify_date
, p.rows
ORDER BY SUM(a.used_pages) DESC
GO
Note that the stored procedure sp_spaceused uses dynamic management view (sys.dm_db_partition_stats) instead of catalog view.
How do you get increment rowcount total in a while loop fowhen you are deleting records from a one table and in the end you want to print the total number of records deleted.
@Joel
See if @ROWCOUNT works in your situation.
how to get number of row intable and use it in gui application
All,
Please can anyone help me out for the way out for writing a procedure for searching numbers in all tables in a database having multiple schemas.
Hoping that you all champs can help me out as I am new bee.
Rgds,
BR..
Follow this post. It will help you in getting what you want. But you need to change the code little bit. Let me know if you find any difficulties
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
Hi All,
In a table A, there are 3 columns and in 500 records like below
Table A
a1 a2 a3
1 4 6
2 5 7
7 3 9
etc……
My Question is:
How to write select statment do display 2,3,9 numbers records present in a table.
Hi Bala,
Could you please explain, what is the logic behind it?
Tejas
I have a query which is belongs to compare the one table columns count in the diff database.and i need to find out if there is any diff between the database count need to display the no of count with coulmns.
Any one can help to get the result.
Please refer the system sp like
sp_columns ‘table_name’
which give u total columns of table.
Hi guys,
I have read thru this whole thread and all the solutions are great when it comes to reading table properties from the system.
I still do not see a solution on how to get the information from a table generated from a select statement.
Example:
select * from tbl_1 join tbl_1 and tbl_2
(two tables, one result set)
I need the number of columns and all properties for this result set. remember this is a on the fly table not in the information_schema.
Can it be done without converting it to a temp table?
@Dragonvic
Instead of using *, specify the COLUMN-list. If two COLUMNs have the same name, give them unique names manually.
Thank you Brian,
but I think you misunderstood my question. I am trying to display property information on a result set like it is done on an existing table:
select *
from information_schema.columns
where table_catalog = ‘your_table’
@Dragonvic
What do you mean by “property information”?
Also, please given an example of the problem and the way you want it to look. I do not understand the issue.
I did give an example…
select *
from information_schema.columns
where table_catalog = ‘your_table’
where it says ‘your_table’ enter any one of your tables.
Tha query will give you properties for that table (columns, keys, values, etc.)
I want to pull the same properties from a result set that is not already a table.
example:
select col1, col2, col3 …
from tbl1 join tbl1 on tbl2.c1 = tbl1.c1
@Dragonvic
I don’t understand what you want to see. If the query result is a pseudo-table for this example, the properties of the returned COLUMNs would be same as the COLUMNs in the original TABLEs. Hence, INFORMATION_SCHEMA has it. IOW, select *
from information_schema.columns
where table_catalog IN (‘tbl1′, ‘tbl2′)
Without using a temporary table it is not possible
But Brian already told you the effecient method
That helps a bit, however, the actual tables (tbl1 and tbl2) are not native to sql, they are being imported using OPENROWSET, coming from a DBF and from an XLS. I need the ability in SQL to let me know how many columns these imports have so I can properly append to the correct local tables.
@Dragonvic
So, you want to know if there is a way to extract some schema information via OPENROWSET?
I have no idea.
No, that’s NOT what I was asking. I wnat to get the info from temp tables. But I think I know how, openrowset saves the data in a temp table.
I know that you can pull information_schema from tempdb. The new challenge would be to hit the right table because tempdb saves the table name diferently from what you would name it.
@dragonvic
See, i knew i was confused. I didn’t get that from your original question. Unfortunately, i am no expert, and i do not know the answer to your question.
Hi Pinal
how are you
in my website i want to shoot auto mail after every day which
content the total records of the table which enter in privious
privious day can you please tell me how can i do this
waiting to your reply its urgent
and also please tell me how can i find previous day date to current date so that make a serch
where
date_col>=dateadd(day,datediff(day,0,getdate())-1,0) and
date_col<dateadd(day,datediff(day,0,getdate()),0)
Hello Dev,
You can get the date of previous day by subtracting 1 from GETDATE() as below:
SELECT GETDATE()-1
You can write the WHERE clause as below:
WHERE datecolumn >= CONVERT(VARCHAR(10), GETDATE()-1) AND datecolumn < CONVERT(VARCHAR(10), GETDATE())
Regards,
Pinal Dave
hi Pinal,
I am writing to the query given below
select * from tblqcontacts
WHERE txtDate = CONVERT(VARCHAR(10), GETDATE()-1)
to find the previous date all records in a table
but there is an error occurs which is
“Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”
what i can do to find all record of previous date of current date
waiting to your reply
@dev1
Why are you CONVERTing to VARCHAR? Remove the CONVERT and there is no error.
To change a data, perhaps DATEADD() would work better for you.
The effecient method is
select * from tblqcontacts
WHERE
txtDate >=dateadd(day,datediff(day,0,getdate())-1,0) and
txtDate <dateadd(day,datediff(day,0,getdate()),0)
Hi pinal
Txtdate is a datetime column in querry
also please tell me how can i find total records of a table through sql querry
Hello Dev,
The updated query is following:
select * from tblqcontacts
WHERE txtDate >= CONVERT(VARCHAR(10), GETDATE()-1,101) AND txtDate < CONVERT(VARCHAR(10), GETDATE(),101)
Regards,
Pinal Dave
I have 10 rows with 3 columns in a table. i have to retrive the field values when column1 is x. In column1 many fields has value as x. Help me to form the SQL query
where column1=’X’
can anybody solve this query, i have an employee table and i need to show all the data of those Employees who are Emp_Status ‘P’ (PERMANENT) and’B’ (PROBATIONARY)
Is this?
Where Emp_Status =’P’ and Emp_Status =’B’
Hi Pinal
i want to shoot a mail once in a day only through asp.net and
sql can you please tell me whats the query for this
waiting to your reply
Dev
Hi,
Why don’t you configure mail in SQL SERVER and develop a Stored Procedure which will be schedule to run at once a day.
This will reduce effort to maintain your task.
To configure mail in SQL, please refer: http://www.sqlyoga.com/2009/05/sql-server-configure-database-mail-with.html
Thanks,
Tejas
Hi i dont know how can configure mail server in sql server
can u please tell me
Dev1
Hello Dev,
Please see my following article:
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
Regards,
Pinal Dave
i am using sql server 2000
Great job, works like a charm.
Hello, -How i can search the special characters (char(10),char(13),…) in every table and field in a SQL Server Database ?
Thanks, S.P.
Refer this post and change your search string to char(10) or char(13)
http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx
hi pinal,
I have 1 table which contain DateTime, Enployee Number and usetype (used for IN or OUT) column, this is for time and attendance. Now I want to know how many employees are inside the campus and their employee ids. I am trying it with max(intime)>max(outtime) but i m not getting the desired result.
sample data-
emp no occurdatetime usetype (1 for IN,2
for OUT)
2099 2009-01-28 11:25:21.000 1
3652 2009-01-28 11:26:12.000 1
3607 2009-01-28 11:26:13.000 1
6270 2009-01-28 11:26:39.000 2
4489 2009-01-28 11:26:53.000 1
6536 2009-01-28 11:27:00.000 1
1742 2009-01-28 11:27:02.000 2
2579 2009-01-28 11:27:09.000 1
3853 2009-01-28 11:28:00.000 2
5762 2009-01-28 11:29:09.000 1
5668 2009-01-28 11:29:31.000 2
4956 2009-01-28 11:31:43.000 2
and how to convert this datetime data formate to Date and time separately.
Please give me some clue to move further.
thanks
Can you post some sample data with expected result for the above sample data?
Thank you so much! great help
Thank you for this one…
Thanks you. Great query. Works without any trouble.
How do u calculating the columns using above query………
i am working on SQL. i want to set a counter for primary key which has some data input, so later when i add other rows the primary kry should increase automatically.. Primary key is “Prot1″
Nxt should be “Prot2″
Two options
1 Use identity column and when you select it prefix prot with it
2 Use identity column and a another computed column that has the definition of
col as ‘Prot’+cast(idcol as varchar(10))
thanh you. do you mean to say that while definig the col i should name col as
‘Prot’+cast(idcol as varchar(10))?
Yes. it is
Great, very useful blog.
Hi there!,
Here you can find the sql command to get number of COLUMNS in a table….
select TABLE_NAME, count(*) COLUMNS1 from all_tab_columns where owner=’EMP’
GROUP BY TABLE_NAME
ORDER BY COLUMNS1
/
Hi,
I have a requirement like, I want to know the number of columns in a temp table.
Thanks in advance
Madan
Hi,
I used the below queries to fetch the number of columns in a temp table.
select count(*) from information_schema.columns
where table_name = ‘#temptable’
select count(*) Noofcolumns from SYSCOLUMNS
where id=(select id from SYSOBJECTS where name=’#temptable’)
both the above queries didn’t give the desired result.
Please tell me how to retrieve the number of columns in a temp table.
Thanks in advance,
Madan
or
exec sp_columns ‘temporary table’
How to display count and names of Databases in SQL Server
select name from sys.databases
Im having some errors here:
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ‘ KB’, ”) AS integer) DESC
DROP TABLE #temp
What names should i use? i dont understand this part…
Thanks for reading!
I have two table a where the coulmn is date and userid and another table emp_info contain userid,name dept now I want to find the information from emp_info who is absent in table a between two date where I may assign the holiday.
Please help me
lots of thanks for ur work.. i got my problem solved in short time for finding numbers of rows of each table.
Hi Pinal,
I am having a strange problem when assigning foreign key to a table.
the error message no.1767: foreign key references invalid table table1.
error.1750:Could not create constraint. See previous errors.
although the table table1 is already created with the primary key which the foreign key is referencing.
Please help me out.
Is the datatype of the column being referenced same as the one on primary key?
How to find number of columns without using count(*) ?
Reply soon.
in SSRS to count rownumber
=Runningvalue(a.value, count, “Dataset1″)
or
=runningvalue(a.value, countdistrict, “Datasetname”)
or
=runningvalue(a.value, sum, “groupname”)
Hi,
How to get first 10 columns from a table without mentioning the column name in sql query
You need to use dynamic sql
This may help you
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/select-data-from-top-n-columns.aspx
—Try for Pubs database which has default schema name dbo or make a table with default schema else you need to add parameter for procedure for schema.
CREATE procedure TopNcolumns
(
@tableName varchar(100),
@n int
)
as
Declare @s varchar(2000)
set @s=”
If @n>0
Begin
DECLARE @column_n varchar(30)
declare @temp int
set @temp=1
DECLARE @getcolumn_n CURSOR
SET @getcolumn_n = CURSOR FOR
SELECT column_name
FROM information_schema.columns where table_name=@tableName order by
ordinal_position
OPEN @getcolumn_n
FETCH NEXT
FROM @getcolumn_n INTO @column_n
WHILE @temp<=@n
BEGIN
set @temp=@temp+1
set @s=@s+','+ @column_n
PRINT @column_n
FETCH NEXT
FROM @getcolumn_n INTO @column_n
END
CLOSE @getcolumn_n
DEALLOCATE @getcolumn_n
Set @s=substring(@s,2,len(@s)-1)
Exec ('Select '+@s+' from '+@tablename)
End
Else
Print ' 0 clumn passed as parameter'
—-now try this…….
exec TopNcolumns 'jobs' ,3
Please use for above code it changed to some other during copy paste into HTML i believe.
Set @s= ” –single quote twice.
select top(10) column_name from information_schema.columns where table_name = ‘yourtablename’
Hi all
when i export to csv file from ssrs2008 one extra row is displaying header, how to hide that particular row ?????????
Thank
SELECT sysobjects.Name [TableName], sysindexes.Rows
FROM sysobjects
JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE type = ‘U’ AND sysindexes.IndId < 2
ORDER BY 1,2
How to get number of columns from select query :
Example:
select xx, yy from trade where ….
number of columns is 2 in this case, is there a way to find out dynamically?? Please help
Why do you want to do this?
To know the number of columns in order to create a matrix. Dont want the user to specify it.
How can we count No of rows in table without using Count (*) or without Count keyword?
Query to find table size for all tables of each database in a server:
DECLARE @DatabaseName VARCHAR(100)
DECLARE @SQL VARCHAR(max)
–Main table to keep all data
CREATE TABLE #TempTableMain
(
databaseName varchar(100),
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
–table to keep data per database and then truncate for the next database
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
–table to store tablenames in a particular database
create table #tableName (name varchar(100))
–Cursor for running query per database
DECLARE databaseCursor CURSOR
FOR select [name] from msdb.sys.databases
where [name] not in (‘master’,'tempdb’,'model’,'msdb’)
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @DatabaseName
WHILE (@@Fetch_Status >= 0)
BEGIN
–storing tableNames of a particuler database in a temp table
SELECT @SQL = ‘use ‘+@DatabaseName+’
insert into #tableName
select [name] from sys.tables’
exec(@SQL)
–soring table information in temp table
SELECT @SQL =
COALESCE(@SQL + CHAR(13) + ‘ ‘ ,”) +
‘
use ‘+ @DatabaseName+’
INSERT #TempTable
EXEC sp_spaceused ‘+name
from #tableName
print @SQL
exec (@SQL)
–storing data for the current database in the main table
INSERT #TempTableMain
select @DatabaseName,* from #TempTable
–truncating temp tables for next run
truncate table #tableName
truncate table #TempTable
FETCH NEXT FROM databaseCursor INTO @DatabaseName
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
–tables to get size in MB and GB
–this part is optional and configurable as per user and usage
CREATE TABLE #TempTableMainMB
(
databaseName varchar(100),
tableName varchar(100),
numberofRows varchar(100),
reservedSizeMB varchar(50),
dataSizeMB varchar(50),
indexSizeMB varchar(50),
unusedSizeMB varchar(50)
)
CREATE TABLE #TempTableMainGB
(
databaseName varchar(100),
tableName varchar(100),
numberofRows varchar(100),
reservedSizeGB varchar(50),
dataSizeGB varchar(50),
indexSizeGB varchar(50),
unusedSizeGB varchar(50)
)
insert #TempTableMainMB
select * from #TempTableMain
–Remove ‘KB’ from tables
update #TempTableMainMB
set reservedSizeMB=REPLACE(reservedSizeMB,’ KB’,”),
dataSizeMB=REPLACE(dataSizeMB,’ KB’,”),
indexSizeMB=REPLACE(indexSizeMB,’ KB’,”),
unusedSizeMB=REPLACE(unusedSizeMB,’ KB’,”)
update #TempTableMainMB
set reservedSizeMB=cast(CAST(reservedSizeMB as INT)/1024 AS varchar(100)),
dataSizeMB=cast(CAST(dataSizeMB as INT)/1024 AS varchar(100)),
indexSizeMB=cast(CAST(indexSizeMB as INT)/1024 AS varchar(100)),
unusedSizeMB=cast(CAST(unusedSizeMB as INT)/1024 AS varchar(100))
insert #TempTableMainGB
select * from #TempTableMainMB
update #TempTableMainGB
set reservedSizeGB=cast(CAST(reservedSizeGB as INT)/1024 AS varchar(100)),
dataSizeGB=cast(CAST(dataSizeGB as INT)/1024 AS varchar(100)),
indexSizeGB=cast(CAST(indexSizeGB as INT)/1024 AS varchar(100)),
unusedSizeGB=cast(CAST(unusedSizeGB as INT)/1024 AS varchar(100))
–display of results
SELECT * FROM #TempTableMain order by 1
SELECT * FROM #TempTableMainMB ORDER BY 1
SELECT * FROM #TempTableMainGB ORDER BY 1
–Final cleanup
DROP TABLE #TempTable
DROP TABLE #TempTableMain
DROP TABLE #TempTableMainMB
DROP TABLE #TempTableMainGB
drop table #tableName
How to Display Table’s column as row
Ex: I Have a Table Employee
Emp_id Emp_Name
101 Santosh
102 Vijay
103 Anilkumar
Now i wanna to display all records of Emp_Name field as horizantally. Like:
Santosh Vijay Anilkumar ………………
Please Help me
Thanks
Santos S.Pawar
You are the best . You save my life every day Thank you..
the no of colunms in table are called?
hi,
how to find the table estimate size based on the number of rows as input. If I give no of rows per a table, it should give estimated size of that table.
No of columns in a table can be returned using the below query:
Select count(*) from user_tab_columns where table_name = ‘tablename’;
[...] Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest… [...]
Awesomw sir…….
How can we know the maximum number of rows sql server 2005 can hold for any package as oracle.
CREATE FUNCTION FN_GET_AGE_YMD
(
@IP_DOB DATETIME,
@IP_TILLDATE DATETIME
)
RETURNS TABLE
AS
RETURN(
SELECT YEARS,MONTHS,DAYS,DATEDIFF(HOUR,CURDATE,@IP_TILLDATE)-24*DAYS AS HOURS, 0 AS MINUTES, 0 AS SECONDS
FROM(
SELECT *,
CASE WHEN DATEPART(HOUR,CURDATE)>DATEPART(HOUR,@IP_TILLDATE)
THEN DATEDIFF(DAY,CURDATE,@IP_TILLDATE)-1
ELSE DATEDIFF(DAY,CURDATE,@IP_TILLDATE) END DAYS
FROM(
SELECT *,DATEADD(MONTH,MONTHS,CURYEAR)CURDATE
FROM(
SELECT *,CASE WHEN DAY(@IP_DOB)>DAY(@IP_TILLDATE) OR
(DAY(@IP_DOB)=DAY(@IP_TILLDATE)
AND DATEPART(HOUR,@IP_DOB)>DATEPART(HOUR,@IP_TILLDATE))
THEN DATEDIFF(MONTH,CURYEAR,@IP_TILLDATE)-1
ELSE DATEDIFF(MONTH,CURYEAR,@IP_TILLDATE) END MONTHS
FROM(
SELECT *,DATEADD(YEAR,YEARS,@IP_DOB)CURYEAR
FROM(
SELECT CASE WHEN MONTH(@IP_DOB)>MONTH(@IP_TILLDATE) OR
(MONTH(@IP_DOB)=MONTH(@IP_TILLDATE)
AND DAY(@IP_DOB)>DAY(@IP_TILLDATE)) OR
(MONTH(@IP_DOB)=MONTH(@IP_TILLDATE)
AND DAY(@IP_DOB)=DAY(@IP_TILLDATE)
AND DATEPART(HOUR,@IP_DOB)>DATEPART(HOUR,@IP_TILLDATE))
THEN DATEDIFF(YEAR,@IP_DOB,@IP_TILLDATE)-1
ELSE DATEDIFF(YEAR,@IP_DOB,@IP_TILLDATE) END YEARS
)A)B)C)D)E)