European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2016 Hosting - HostForLIFE.eu :: SQL Server Important System Views and Tables

clock July 17, 2019 12:08 by author Peter

In this article I have listed few methods to know about the list of database, tables, views,etc.., It will be very useful when we trace the database objects in the query window. Even though it can be accessible in the sql server object explorer, but when we write the query it can be customized. That means it can filter the result set based on our requirement.

How to list out the available database in the SQL Server current connection?
Method 1
SP_DATABASES 

Method 2
SELECT name FROM SYS.DATABASES 

Method 3
SELECT name FROM SYS.MASTER_FILES 

Method 4
SELECT * FROM SYS.MASTER_FILES -- Type=0 for .mdf and type=1 for .ldf 
The sp_databases is a system stored procedure it can be listed the database with the size.
The sys.databases will list the databases, created date, modified date and database id along with the other information

The SYS.MASTER_FILES will query the database details like the database id, size, physical storage path and list both mdf and ldf.

How to list the user tables in the database?
The following method can be used to get the list of user tables in the SQL server.

Method 1
SELECT name FROM SYS.OBJECTS WHERE type='U' 

Method 2
SELECT NAME FROM SYSOBJECTS WHERE xtype='U' 

Method 3
SELECT name FROM SYS.TABLES 

Method 4
SELECT name FROM SYS.ALL_OBJECTS WHERE type='U' 

Method 5
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' 

Method 6
SP_TABLES 

How to list out the Stored Procedures in the database?
Method 1

SELECT name FROM SYS.OBJECTS WHERE type='P' 

Method 2
SELECT name FROM SYS.PROCEDURES 

Method 3

SELECT name FROM SYS.ALL_OBJECTS WHERE type='P'

Method 4
SELECT NAME FROM SYSOBJECTS WHERE xtype='P'

Method 5
SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' 

The SYS.OBJECTS table has the common table that has the list for all the procedure, table, triggers, views,etc.., Here procedure can be filtered using the type='p'.

The Information_schema.routines is a view that has used in the SQL server 7.0 version. Now exclusive table available for the stored procedure.

How to list all Views in the database?
Method 1
SELECT name FROM SYS.OBJECTS WHERE type='V' 

Method 2
SELECT name FROM SYS.ALL_OBJECTS WHERE type='V' 

Method 3
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS 

Method 4
SELECT name FROM SYS.VIEWS

How to list out the Functions in the database?
Method 1

SELECT name FROM SYS.OBJECTS WHERE type='IF' -- inline function 

Method 2
SELECT name FROM SYS.OBJECTS WHERE type='TF' -- table valued function 

Method 3
SELECT name FROM SYS.OBJECTS WHERE type='FN' -- scalar function 

Method 4
SELECT name FROM SYS.ALL_OBJECTS WHERE type='IF' -- inline function 

Method 5
SELECT name FROM SYS.ALL_OBJECTS WHERE type='TF' -- table valued function 

Method 6
SELECT name FROM SYS.ALL_OBJECTS WHERE type='FN' -- scalar function

Method 7
SELECT Routine_name FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'

Note: IF - Inlined Function, TF- Table valued function, FN- Scalar Function

How to get the Triggers in the database?
Method 1

SELECT * FROM SYS.TRIGGERS

Method 2
SELECT * FROM SYS.OBJECTS WHERE type='TR'

How to get the triggers in a table?
Method 1

SP_HELPTRIGGER Products

Method 2
SELECT * FROM SYS.TRIGGERS WHERE parent_id = object_id('products')

How to get the columns in a table?
Method 1

SP_HELP Products

Method 2
SP_COLUMNS Products

Method 3
SELECT * FROM SYS.COLUMNS WHERE object_id = object_id('Products')

Method 4
SELECT COLUMN_NAME,Ordinal_position,Data_Type,character_maximum_length 
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME='Products'

How to find the Columns in the table?
Method 1

SELECT O.name FROM SYS.OBJECTS O INNER JOIN SYS.COLUMNS C  
ON C.Object_ID =O.Object_ID  
WHERE C.name LIKE '%ShipName%'

Method 2

SELECT OBJECT_NAME(object_id) AS [Table Name]  
FROM SYS.COLUMNS  
WHERE name LIKE '%ShipName%'

Method 3
SELECT TABLE_NAME  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE COLUMN_NAME LIKE '%ShipName%'

How to get the Total rows in the table?
Method 1

SELECT COUNT(@@ROWCOUNT) FROM Products

Method 2
SELECT COUNT (ProductID) FROM Products

Method 3
SELECT OBJECT_NAME(id) AS [Table Name],rowcnt  
FROM SYSINDEXES  
WHERE OBJECTPROPERTY(id,'isUserTable')=1 AND indid < 2  
ORDER BY rowcnt DESC

Method 4
SELECT rowcnt FROM sysindexes  
WHERE id = OBJECT_ID('Products') AND indid < 2

Method 5
SELECT OBJECT_NAME(OBJECT_ID) TableName,row_count  
FROM sys.dm_db_partition_stats  
WHERE object_id = object_id('Products') AND index_id < 2

How to get the Check Constraints in the database?
Method 1

SELECT * FROM SYS.OBJECTS WHERE type='C'

Method 2
SELECT * FROM sys.check_constraints

How to find the Indexes in the table?
Method 1

sp_helpindex Products

Method 2
SELECT * FROM sys.indexes  
WHERE object_id = object_id('products')

How to view the View schema definition?
Method 1

SELECT OBJECT_NAME(id) AS [View Name],text  
FROM SYSCOMMENTS  
WHERE id IN (SELECT object_id FROM SYS.VIEWS)

Method 2
SELECT * FROM sys.all_sql_modules  
WHERE object_id IN (SELECT object_id FROM SYS.VIEWS)

Method 3
SP_HELPTEXT ViewName

How to find the table used in the stored procedure?
Method 1

SELECT OBJECT_NAME(id) FROM SYSCOMMENTS S INNER JOIN SYS.OBJECTS O ON O.Object_Id = S.id 
WHERE S.text LIKE '%Products%' 
AND O.type='P'

I hope that the above methods will help you more when you work the query window to find the database objects. Please post your feedback and corrections about this article.



SQL Server 2016 Hosting - HostForLIFE.eu :: Aggregate Functions in SQL Server

clock July 3, 2019 11:58 by author Peter

What are Aggregate Functions in SQL Server? This article helps you to explore various Aggregate Functions in SQL Server.

What are Aggregate Functions?
I can give different definitions.

  • Aggregate functions are built in sql server functions.
  • Aggregate functions are applied to sets of records rather than to a single record.
  • Aggregate functions performs a computation on a set of values rather than on a single value.
  • Aggregate functions uses to summarize data.
  • Aggregate functions perform a calculation on a set of values and return a single value.

Getting Started
The information in multiple records are processed in a particular manner and then displayed in a single record answer.
Aggregate functions are often used in conjuction with GROUP BY clause.
Aggregate functions cannot be nested. The expression cannot be a subquery.

The list of built in Aggregate functions are:
AVG, CHECKSUM, CHECKSUM_AGG, COUNT, COUNT_BIG, GROUPING, MAX, MIN, SUM, STDEV, STDEVP, VAR, VARP.

AVG in SQL Server
AVG returns the average of the values in expression. The expression must contain numeric values. Null values are ignored.

The syntax: AVG ([ ALL | DISTINCT ] <expression>)
select orderid, avg(UnitPrice) UnitPrice from dbo.[Order Details] group by orderid; 

CHECKSUM in SQL Server
This is a basic hash algorithm usually used to detect changes or consistency in data.

"A digit representing the sum of the correct digits in a piece of stored or transmitted digital data, against which later comparisons can be made to detect errors in the data."

The syntax: CHECKSUM(<expression>, [ ... n] | *)

SELECT CHECKSUM(orderid, UnitPrice, quantity), orderid, UnitPrice, quantity FROM dbo.[Order Details] WHERE orderid = 10248 

CHECKSUM_AGG in SQL Server
The same as CHECKSUM, but the primary difference is that CHECKSUM is oriented around rows, whereas CHECKSUM_AGG is oriented around columns.

The syntax: CHECKSUM( [ALL | DISTINCT] <expression> )
SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details] 
update dbo.[Order Details] set UnitPrice = 15 
where orderid = 10248 and ProductID = 11 
SELECT CHECKSUM_AGG(CAST(UnitPrice AS int)) FROM dbo.[Order Details] 

COUNT in SQL Server

Returns the number of items in expression. The data type returned is of type int.

The syntax: COUNT( [ALL | DISTINCT] <expression> | * )
select COUNT(*), AVG(UnitPrice) from dbo.[Order Details] 

COUNT_BIG in SQL Server
Returns the number of items in a group. The data type returned is of type bigint.

The syntax: COUNT( [ALL | DISTINCT] <expression> | * )
select COUNT_BIG(*), AVG(UnitPrice) from dbo.[Order Details] 

GROUPING in SQL Server
MSDN : Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.

Function adds an extra column to the output of a SELECT statement.

The syntax: GROUPING(<column_name> )
select orderid, sum(UnitPrice) UnitPrice, GROUPING(orderid) 'orderid' 
from dbo.[Order Details] WHERE orderid = 10248 
GROUP BY orderid WITH cube 

MAX in SQL Server

Returns the maximum value from expression. Max ignores any NULL values.

The syntax: MAX( [ALL | DISTINCT] <expression> )
select MAX(QUANTITY) from dbo.[Order Details] 

MIN in SQL Server

Returns the smallest value from expression. Min ignores any NULL values.

The syntax: MIN( [ALL | DISTINCT] <expression> )
select MIN(QUANTITY) from dbo.[Order Details] 

SUM in SQL Server
Returns the total of all values in expression. Sum ignores any NULL values.

The syntax: SUM( [ALL | DISTINCT] <expression> )
select SUM(QUANTITY) from dbo.[Order Details] 

STDEV in SQL Server
Returns the standard deviation of all values in expression. Stdev ignores any NULL values.

The syntax: STDEV( <expression> )
select STDEV(QUANTITY) from dbo.[Order Details]

STDEVP in SQL Server
Returns the standard deviation for the population of all values in expression. Stdevp ignores any NULL values.

The syntax: STDEVP( <expression> )
select STDEVP(QUANTITY) from dbo.[Order Details] 

VAR in SQL Server
Returns the variance of all values in expression. Var ignores any NULL values.

The syntax: VAR( <expression> )
select VAR(QUANTITY) from dbo.[Order Details] 

VARP in SQL Server
Returns the variance for the population of all values in expression. Varp ignores any NULL values.
The syntax: VARP( <expression> )
select VARP(QUANTITY) from dbo.[Order Details] 

 



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in