April 1, 2011 at 11:34 pm
Hi.
For the documentation purpose wants to collect Total tables,procedure, Views, trigger, index, Please anyone could share this script.
Thanks
ananda
April 2, 2011 at 12:27 am
Select out of the system tables which hold the proper data. For example, sysobjects (where type = 'U'), sysindexes, syscomments, etc.
Sql2005 and greater use slightly different table names (sys.indexes for example).
If you are using sql7 (since this forum covers that also), tools like sqldoc probably won't work properly, bu they ought to work just fine on sql2000. Finding prebuilt raw scripts which don't use data management views are getting harder to find anymore., but there are still all over the web.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 11, 2011 at 1:20 pm
If you are using SQL Server 2000 here are some views and tables you can query:
SELECT * FROM sysindexes
SELECT * FROM INFORMATION_SCHEMA.tables
SELECT * FROM INFORMATION_SCHEMA.VIEWS
You can also use the [sysobjects] system table:
SELECT * FROM sysobjects WHERE xtype = 'TR' --this will find all triggers
You can use other filter conditions on the [xtype] column of the sysobjects table.
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined 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
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 11, 2011 at 10:04 pm
Hi,
Thanks for your information, it is very useful.
I' ve found one script for counting total lines of procedure and function but this is not working. please give me any alternative script.
Error
------
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '–'.
select o.name as sp_name,
(len(c.text) – len(replace(c.text, char(10),''))) as lines_of_code,
case when o.xtype = 'P' then 'Stored Procedure'
when o.xtype in ('FN', 'IF', 'TF') then 'Function'
end as type_desc
from sysobjects o
inner join syscomments c
on c.id = o.id
where o.xtype in ('P', 'FN', 'IF', 'TF')
and o.category = 0
and o.name not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition',
'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams')
Thanks
ananda
April 12, 2011 at 6:33 am
I think your '-' got replaced with a double minus, probably office autoformatting. Try replacing it in your script.
April 12, 2011 at 6:52 am
Try this one:
select o.name as sp_name,
(len(c.text) - len(replace(c.text, char(10),''))) as lines_of_code,
case when o.xtype = 'P' then 'Stored Procedure'
when o.xtype in ('FN', 'IF', 'TF') then 'Function'
end as type_desc
from sysobjects o
inner join syscomments c
on c.id = o.id
where o.xtype in ('P', 'FN', 'IF', 'TF')
and o.category = 0
and o.name not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition',
'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams')
Your problem was that you had the wrong dash(-) near len(replace.
You had a dash that represented ASCII character 150 and the minus required is ASCII 45.
Check it out with on this code
PRINT ASCII('–') --this is the one you are using "A DASH"
PRINT ASCII('-') --this is the one SQL requires "A MINUS"
PRINT CHAR(150)
PRINT CHAR(45)
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 12, 2011 at 6:58 am
Hi Ysaias Portes,
Thank you so much, It is working fine....
ananda
April 13, 2011 at 3:31 pm
you can create this view (and modify to add more infromation) in the model database and user databases and then query the view. Works well for searching information about the database. Very simple really. Can me made more complex if necessary.
CREATE VIEW dbo.SchemaView_VW
AS
SELECT
TOP 100 PERCENT obj.name AS table_name,
cols.name AS field_name,
type.name AS field_type,
cols.length AS field_size,
props.[value] AS field_description,
cols.isnullable AS field_nullable,
type.tdefault AS field_default
FROM
dbo.sysobjects obj
INNER JOIN
dbo.syscolumns cols ON obj.id = cols.id
LEFT OUTER JOIN
dbo.sysproperties props ON cols.id = props.id
AND cols.colid = props.smallid
LEFT OUTER JOIN
dbo.systypes type ON cols.xtype = type.xusertype
WHERE
(obj.type = 'U')
ORDER BY
table_name
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply