In this Article, I will explain some important “SQL Server Queries”. I think “most of each SQL Developer” needs these important queries in the daily life!
“How to use below listed SQL Quires”? And “What are the Advantages”?
As per this, I am tried to write these important “SQL Queries” and sharing with you! If you have any other smart query related to this (You can share to me and I will publish this post with your query!)
--GET LIST OF DATABASES EXEC SP_HELPDB -- RESULT name db_size owner dbid created status compatibility_level CLR_Demo_BD 7.00 MB sa 7 Dec 30 2016 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110 Demo 7.00 MB sa 8 Jan 2 2017 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110 master 6.63 MB sa 1 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 110 model 5.31 MB sa 3 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 110 msdb 21.75 MB sa 4 Feb 10 2012 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110 ReportServer 15.19 MB sa 5 Sep 14 2016 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110 ReportServerTempDB 5.38 MB Lenovo-08\Viainda 6 May 26 2016 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110 tempdb 8.75 MB sa 2 Dec 20 2016 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=706, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics 110
-- DISPLAY TEXT FOR PROCEDURE, TRIGGER, VIEW EXEC SP_HELPTEXT @objname = 'Object_Name' --EXAMPLE FOR DISPLAY TEXT EXEC SP_HELPTEXT @objname = 'dbo.SP_Get_Students' --RESULT Text CREATE PROCEDURE SP_Get_Students AS BEGIN SET NOCOUNT ON; -- SELECT STATEMENTS FOR PROCEDURE HERE SELECT * FROM [dbo].[Students] END
--GET SQL SERVER VERSION NAME SELECT @@VERSION AS VersionName --RESULT VersionName Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--GET CURRENT SQL SERVER LANGUAGE SELECT @@LANGUAGE AS CurrentUsedLanguage -- RESULT CurrentUsedLanguage us_english
--GET CURRENT LANGUAGE ID SELECT @@LANGID AS 'LanguageID' --EXAMPLE SET LANGUAGE 'English' SELECT @@LANGID AS 'LanguageID' -- RESULT LanguageID 0
--GET LIST OF DATABASE PROCEDURES SELECT DISTINCT SO.NAME, SO.XTYPE FROM SYSCOMMENTS SC INNER JOIN SYSOBJECTS SO ON SC.ID=SO.ID WHERE SO.XTYPE ='P' --OR -- USING LIKE FOR SYSCOMMENTS SELECT DISTINCT SO.NAME, SO.XTYPE FROM SYSCOMMENTS SC INNER JOIN SYSOBJECTS SO ON SC.ID=SO.ID WHERE SC.TEXT LIKE '%SP_%' -- RESULT NAME XTYPE SP_Email_Approvals P SP_Get_Students P
-- REBUILD ALL DATABASE INDEX GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO EXEC sp_updatestats GO -- RESULT [dbo].[Students] DBCC execution completed. If DBCC printed error messages, contact your system administrator. Updating [dbo].[Students] [PK_Students], update is not necessary... 0 index(es)/statistic(s) have been updated, 1 did not require update. Updating [sys].[queue_messages_1977058079] [queue_clustered_index], update is not necessary... [queue_secondary_index], update is not necessary... 0 index(es)/statistic(s) have been updated, 2 did not require update. Updating [sys].[queue_messages_2009058193] [queue_clustered_index], update is not necessary... [queue_secondary_index], update is not necessary... 0 index(es)/statistic(s) have been updated, 2 did not require update. Updating [sys].[queue_messages_2041058307] [queue_clustered_index], update is not necessary... [queue_secondary_index], update is not necessary... 0 index(es)/statistic(s) have been updated, 2 did not require update. Updating [sys].[filestream_tombstone_2073058421] [FSTSClusIdx], update is not necessary... [FSTSNCIdx], update is not necessary... 0 index(es)/statistic(s) have been updated, 2 did not require update. Updating [sys].[syscommittab] [ci_commit_ts], update is not necessary... [si_xdes_id], update is not necessary... 0 index(es)/statistic(s) have been updated, 2 did not require update. Updating [sys].[filetable_updates_2105058535] [FFtUpdateIdx], update is not necessary... 0 index(es)/statistic(s) have been updated, 1 did not require update. Statistics for all tables have been updated.
--GET ALL TABLE NAME USED IN PROCEDURES WITH GET_Procedures AS ( SELECT oo.name AS GET_UsedTable_name, ROW_NUMBER() OVER(partition by o.name, oo.name ORDER BY o.name, oo.name) AS ROW FROM SYSDEPENDS d INNER JOIN SYSOBJECTS o ON o.id=d.id INNER JOIN SYSOBJECTS oo ON oo.id=d.depid WHERE o.xtype = 'P' AND o.name LIKE '%SP_%' ) SELECT GET_UsedTable_name FROM GET_Procedures WHERE ROW = 1 --RESULT GET_UsedTable_name Students
-- List of Table Primary and Foreign Keys SELECT DISTINCT Constraint_Name AS [ConstraintName], Table_Schema AS [SchemaName], Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Students' GO -- RESULT ConstraintName SchemaName TableName PK_Students dbo Students
--DISABLE ALL CONSTRAINTS OF A TABLE ALTER TABLE Students NOCHECK CONSTRAINT ALL --RESULT Command(s) completed successfully.
--DISABLE ALL CONSTRAINTS OF ALL TABLES EXEC SP_MSFOREACHTABLE 'ALTER TABLE? NOCHECK CONSTRAINT ALL' --RESULT Command(s) completed successfully.
-- CHANGE DEFAULT LANGUAGE FOR SQL SERVER GO EXEC SP_CONFIGURE 'DEFAULT LANGUAGE', 23; GO RECONFIGURE WITH OVERRIDE; GO --RESULT Configuration option 'default language' changed from 0 to 23. Run the RECONFIGURE statement to install.
I hope you are enjoying with this post! Please share with
you friends. Thank you!!