Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Line count counter

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 08:22:07
[code]CREATE TABLE #Info
(
DbName SYSNAME,
ROUTINE_TYPE SYSNAME,
ROUTINE_NAME SYSNAME,
Lines INT
)

EXEC sp_msforeachdb '
INSERT #Info
(
DbName,
ROUTINE_TYPE,
ROUTINE_NAME,
Lines
)
SELECT ''?'',
d.ROUTINE_TYPE,
d.ROUTINE_NAME,
SUM(CASE WHEN d.c10 < d.c13 THEN d.c13 ELSE d.c10 END)
FROM (
SELECT ROUTINE_TYPE,
ROUTINE_NAME,
1 + LEN(ROUTINE_DEFINITION) - LEN(REPLACE(ROUTINE_DEFINITION, CHAR(13), '''')) AS c13,
1 + LEN(ROUTINE_DEFINITION) - LEN(REPLACE(ROUTINE_DEFINITION, CHAR(10), '''')) AS c10
FROM .INFORMATION_SCHEMA.ROUTINES
) AS d
GROUP BY d.ROUTINE_TYPE,
d.ROUTINE_NAME
'
SELECT DbName,
ROUTINE_TYPE,
ROUTINE_NAME,
Lines
FROM #Info
ORDER BY DbName,
ROUTINE_TYPE,
ROUTINE_NAME

DROP TABLE #Info[/code]

E 12°55'05.25"
N 56°04'39.16"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2008-06-13 : 09:53:06
On Sql server 2000...

Msg 1540, Level 16, State 1, Line 2
Cannot sort a row of size 8106, which is greater than the allowable maximum of 8094.

This Peculiar to me?

-------------
Charlie
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-06-13 : 10:45:16
Peso it gives 1 more than actual number of lines.

Also I remember now that once I used the following for the same purpose
create table #myscript_table (sptext varchar(8000))
insert into #myscript_table(sptext)
EXEC sp_helptext mysp

select count(*) from #myscript_table

delete from #myscript_table


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 12:26:04
I think it calculates the right number of rows.

If you have no Cr or Lf, there is only one line of code.
If you have 1 Cr or 1 Lf, there is only two lines of code.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-06-13 : 13:13:38
Can you create this and see?
create procedure mysp
as
select 1
union all
--
select 2

It has only 6 lines

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 13:42:12
[code]DbName ROUTINE_TYPE ROUTINE_NAME Lines
Test PROCEDURE isp_ALTER_INDEX 117
Test PROCEDURE mysp 6
Test PROCEDURE sp_alterdiagram 65[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-06-13 : 13:46:36
quote:
Originally posted by Peso


Test PROCEDURE isp_ALTER_INDEX 117




Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 13:50:32
Still under test database

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-06-13 : 14:13:47
How do you defragment indexes in production?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 14:17:05
Probably with isp_AlterIndex when finished testing.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-06-13 : 14:19:11
So you aren't currently doing any defragmentation? We see major performance problems if we don't do it at least weekly. On some systems, we have to do it daily. We've got so much data pumping into the system, that the indexes become fragmented quickly.

Anyway, sorry to hijack the thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 14:26:54
We use DBCC today once a week. Scheduled with a job.
The largest table where I work today has 50 million records, with an average growth of 60000 records daily, so weekly defrag is ok.

My main concern is that for one customer, there is 70% dynamic sql in whole database.
So a SP can execute differently according to input parameters for each execution.

And the DBA is constantly shrinking both database and log at least twice a week.

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-06-13 : 15:08:42
quote:
Originally posted by Peso

DbName	ROUTINE_TYPE	ROUTINE_NAME	Lines
Test	PROCEDURE	isp_ALTER_INDEX	117
Test	PROCEDURE	mysp		6
Test	PROCEDURE	sp_alterdiagram	65


E 12°55'05.25"
N 56°04'39.16"

Well. Again a behavioural change

It gives me 7 in SQL Server 2000 Developer Edition

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 15:27:27
Run this example to verify.
Please notice the placement of single quotes
declare @a varchar(8000),
	@b varchar(8000)

set @a = 'create procedure mysp
as
select 1
union all
--
select 2'

select	datalength(@a),
	len(@a),
	LEN(replace(@a, char(10), '')),
	LEN(replace(@a, char(13), '')),
	len(@a) - LEN(replace(@a, char(13), '')) + 1
	

set @b = 'create procedure mysp
as
select 1
union all
--
select 2
'
select	datalength(@b),
	len(@b),
	LEN(replace(@b, char(10), '')),
	LEN(replace(@b, char(13), '')),
	len(@b) - LEN(replace(@b, char(13), '')) + 1


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-13 : 15:28:31
I get 6 for @a, and 7 for @b.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-06-13 : 16:02:19
quote:
Originally posted by Peso

I get 6 for @a, and 7 for @b.


E 12°55'05.25"
N 56°04'39.16"

Yes it is.
But still I get one more than actual number when I execute your code
The sample sp is what you have assigned in @a

My code result
            
----------- 
6

(1 row(s) affected)


Your code result
DbName                                                                                                                           ROUTINE_TYPE                                                                                                                     ROUTINE_NAME                                                                                                                     Lines       
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- 
test                                                                                                                             PROCEDURE                                                                                                                        mysp                                                                                                                             7
master                                                                                                                           PROCEDURE                                                                                                                        mysp                                                                                                                             7
model                                                                                                                            PROCEDURE                                                                                                                        mysp                                                                                                                             7
msdb                                                                                                                             PROCEDURE                                                                                                                        mysp                                                                                                                             7
Northwind                                                                                                                        PROCEDURE                                                                                                                        mysp                                                                                                                             7
pubs                                                                                                                             PROCEDURE                                                                                                                        mysp                                                                                                                             7
tempdb                                                                                                                           PROCEDURE                                                                                                                        mysp                                                                                                                             7

(7 row(s) affected)


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site