SQL Query

• Rhythmfree

  Valued Member

  Points: 51

  More actions

  February 4, 2009 at 10:42 pm

  #200890

  Hi Friends,

  I need a query which will display the details of database like below.

  this will take Database name as Input parameter.

  [DatabaseName] Details

  -----------------------

  Nos. of Tables: Nos. of Views:

  Nos. of Procedure: Nos. of Function:

  Nos. of Trigger: Nos. of Schema:

  Nos. of Rules: Nos. of Users:

  I am new to SQL query. Please help me.

  Thanks,
  Rhythmfree

  Love is life & love is blind, love demands giving and sacrifice, still love is love. Nobody knows what is it..?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 4, 2009 at 10:50 pm

  #938605

  look at the sysobjects table.

  there is a column there called "xtype". you could count(*) from sysobjects where xtype='U' for example, to get all user tables. V for views, you can look in google or books online for the rest of the xtype definitions.

  alternatively, there is a suite of tables called INFORMATION_SCHEMA.TABLES, ROUTINES, etc, which also have all the objects in it, but in a more readable form. you can count from those tables as well.

  I leave it to you to show us what you've tried after researching this a bit more.

  Good Luck!

  Rhythmfree (2/4/2009)

  ---

  Hi Friends,

  I need a query which will display the details of database like below.

  this will take Database name as Input parameter.

  [DatabaseName] Details

  -----------------------

  Nos. of Tables: Nos. of Views:

  Nos. of Procedure: Nos. of Function:

  Nos. of Trigger: Nos. of Schema:

  Nos. of Rules: Nos. of Users:

  I am new to SQL query. Please help me.

  Thanks,

  Sunayana Pandit(seenu)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Rhythmfree

  Valued Member

  Points: 51

  More actions

  February 4, 2009 at 11:03 pm

  #938607

  Can you please give me the query,

  I am getting individual result for this...

  Thanks,
  Rhythmfree

  Love is life & love is blind, love demands giving and sacrifice, still love is love. Nobody knows what is it..?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 4, 2009 at 11:08 pm

  #938609

  because this question looks a lot like what a homework assignment would require so that you get used to querying and displaying data, you'll need to show us what you have tried so far.

  Show us what you 've done so far. Hint: are you familiar with the UNION operator so you can join multiple SELECT statements?

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Rhythmfree

  Valued Member

  Points: 51

  More actions

  February 4, 2009 at 11:24 pm

  #938611

  Ok,

  Let me try..Lowell...

  If not you need to guide me....

  is it ok...

  Thanks,
  Rhythmfree

  Love is life & love is blind, love demands giving and sacrifice, still love is love. Nobody knows what is it..?

• Hari.Sharma

  SSCertifiable

  Points: 6904

  More actions

  February 4, 2009 at 11:58 pm

  #938616

  Hi Seenu,

  Try this query and modify accordingly

  SELECT COUNT(*),type_desc FROM sys.objects

  GROUP BY type_desc

  Cheers,
  Hari
  Tips & Tricks for SQL BI Developers

• Rhythmfree

  Valued Member

  Points: 51

  More actions

  February 5, 2009 at 12:16 am

  #938621

  Hari,

  I created following Proc but i am unable to get user, schema and rules value. please have a look on this..and suggest me how can i get these values...

  CREATE PROCEDURE GetDatabaseObjectCount

  (

  @DatabaseName NVARCHAR(50)

  )

  AS

  SET NOCOUNT ON

  DECLARE @sqlNVARCHAR(4000)

  ,@TotalTableNVARCHAR(20)

  ,@TotalViewNVARCHAR(20)

  ,@TotalProcedureNVARCHAR(20)

  ,@TotalFunctionNVARCHAR(20)

  ,@TotalTriggerNVARCHAR(20)

  ,@TotalSchemaNVARCHAR(20)

  ,@TotalRulesNVARCHAR(20)

  ,@TotalUsersNVARCHAR(20)

  SET @sql = ''

  BEGIN

  DECLARE @Temp TABLE

  (TotalTable INT, TotalView INT, TotalProcedure INT, TotalFunction INT

  ,TotalTrigger INT, TotalSchema INT, TotalRules INT, TotalUsers INT)

  INSERT INTO @Temp(TotalTable)

  SELECT COUNT(Name) FROM SYS.SYSOBJECTS

  WHERE XType = 'U'

  UPDATE @Temp

  SET TotalView = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'V')

  ,TotalProcedure = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'P')

  ,TotalFunction = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'FN')

  ,TotalTrigger = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'TR')

  SELECT @TotalTable = MAX(TotalTable), @TotalView = MAX(TotalView)

  ,@TotalProcedure = MAX(TotalProcedure), @TotalFunction = MAX(TotalFunction)

  ,@TotalTrigger = MAX(TotalTrigger), @TotalSchema = MAX(ISNULL(TotalSchema,0))

  ,@TotalRules = MAX(ISNULL(TotalRules,0)), @TotalUsers = MAX(ISNULL(TotalUsers,0))

  FROM @Temp

  SET @sql = @DatabaseName + ' Details

  ------------------

  Nos. of Tables: ' + @TotalTable+ ' Nos. of Views: ' + @TotalView + '

  Nos. of Procedure: ' + @TotalProcedure+ 'Nos. of Function: ' + @TotalFunction + '

  Nos. of Trigger: ' + @TotalTrigger+ 'Nos. of Schema: ' + @TotalSchema + '

  Nos. of Rules: ' + @TotalRules+ ' Nos. of Users ' + @TotalUsers + '

  '

  PRINT @sql

  END

  /*

  EXEC GetDatabaseObjectCount 'TEMPDATABASE'

  */

  Thanks,
  Rhythmfree

  Love is life & love is blind, love demands giving and sacrifice, still love is love. Nobody knows what is it..?

• raj acharya

  SSCrazy

  Points: 2805

  More actions

  February 5, 2009 at 1:31 am

  #938638

  good query but did you try to entering in that another database name ?

  Raj Acharya

• Rhythmfree

  Valued Member

  Points: 51

  More actions

  February 5, 2009 at 1:37 am

  #938639

  Raj, Hi...

  Yes, Its working fine with me..

  If you wants you can..but 1st you need to run and test it..

  but I think it can be very easy in CET...

  I only listen about this. but never tried it..

  If I could have knew it, I could have implemented it rather than

  general..

  I tried it but no the result...so..

  Raj ..do you know CET...if you, can you tell me how to handle sp in CET...;)

  Thanks,
  Rhythmfree

  Love is life & love is blind, love demands giving and sacrifice, still love is love. Nobody knows what is it..?

• maechismo_8514

  SSCoach

  Points: 17510

  More actions

  February 5, 2009 at 1:47 am

  #938640

  TotalFunction = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'FN' OR )

  Don't you have to count total functions? Here you are counting only a scalar Function:)

• Rhythmfree

  Valued Member

  Points: 51

  More actions

  February 5, 2009 at 1:58 am

  #938647

  Oh Ya, I miss :crying:..

  I need to count all the function.

  any thing else i missed...Thanks Kriss...

  Thanks,
  Rhythmfree

  Love is life & love is blind, love demands giving and sacrifice, still love is love. Nobody knows what is it..?

• raj acharya

  SSCrazy

  Points: 2805

  More actions

  February 5, 2009 at 2:15 am

  #938657

  hi,

  i think in your above query you have do extra work and for function count may be you get wrong result

  try below query as per your requirement only schema one missing i m searching for that

  DECLARE @DBNAME VARCHAR(50)

  SET @DBNAME = 'rtemp'

  DECLARE @Rsql VARCHAR(MAX)

  SET @Rsql = '

  SELECT (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''U'' AND TYPE <> ''S'') AS TABLE_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''P'' AND TYPE <> ''S'') AS SP_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE_DESC LIKE ''%FUNCTION%'' AND TYPE <> ''S'') AS FUNCTION_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''V'' AND TYPE <> ''S'') AS VIEW_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''TR'' AND TYPE <> ''S'')AS TRIGGER_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.SYS.SYSUSERS WHERE UID 0) AS USER_COUNT

  '

  PRINT @Rsql

  EXEC (@rsql)

  Raj Acharya

• maechismo_8514

  SSCoach

  Points: 17510

  More actions

  February 5, 2009 at 2:15 am

  #938658

  SET TotalView = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'V')

  ,TotalProcedure = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'P')

  ,TotalFunction = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'FN')

  ,TotalTrigger = (SELECT COUNT(Name) FROM SYS.SYSOBJECTS WHERE XType = 'TR')

  Where are you calculating users,schema, rules here?

• raj acharya

  SSCrazy

  Points: 2805

  More actions

  February 5, 2009 at 2:28 am

  #938666

  CHECK FOLLOWING AND JUST ENTER DATABASE NAME YOU WILL GET WHAT YOU WANT .....

  in schema and user i have ommited default system schema or user

  DECLARE @DBNAME VARCHAR(50)

  SET @DBNAME = 'rtemp'

  DECLARE @Rsql VARCHAR(MAX)

  SET @Rsql = '

  SELECT (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''U'' AND TYPE <> ''S'') AS TABLE_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''P'' AND TYPE <> ''S'') AS SP_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE_DESC LIKE ''%FUNCTION%'' AND TYPE <> ''S'') AS FUNCTION_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''V'' AND TYPE <> ''S'') AS VIEW_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.objects WHERE TYPE = ''TR'' AND TYPE <> ''S'')AS TRIGGER_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.SYS.SYSUSERS WHERE UID 0) AS USER_COUNT,

  (SELECT COUNT(*) FROM '+@DBNAME+'.sys.schemas WHERE SCHEMA_ID < 16384) AS SCHEMA_COUNT

  '

  PRINT @Rsql

  EXEC (@rsql)

  Raj Acharya

• Rhythmfree

  Valued Member

  Points: 51

  More actions

  February 5, 2009 at 2:32 am

  #938669

  Cool Raj..

  Its good one..but you may be missed = in

  WHERE UID = 0) AS USER_COUNT

  Its ok..but as krishna said...other 3...

  how can u resolve it..

  Thanks,
  Rhythmfree

  Love is life & love is blind, love demands giving and sacrifice, still love is love. Nobody knows what is it..?