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)
277 Comments. Leave new
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)
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
—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