OnceSQL Server Full-Text Search with rank values  I wrote a post titled enabling Fulltext search in Azure SQL database discussing Full-Text search in Azure. while using it with one of my databases, needed to show the result of the search ordered by however well they match to the search criteria. in order to sort the result as i need, the best is, get a rank generated for every row and use it for ordering the result. I had used Freetext operate for obtaining the result but if i realized that this can not be achieved using the Freetext function.

The CONTAINSTABLE and FREETEXTTABLE functions return a column named Rank for showing the rank related to the record based on matching. this can be used get the result sorted based on it, showing most relevant records at the top. Remember, the higher value of the Rank generated indicates the best matching.

Now, write the following code:
view plainprint?

    -- Creating a table  
    CREATE TABLE dbo.EmployeeDetails  
    (  
     EmployeeDetailsId int identity(1,1) not null  
     , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)  
     , WorkingExperience nvarchar(4000) not null  
     , ProjectsWorked nvarchar(4000) not null  
     , Resume nvarchar(max)   
    )  
    GO  
      
    CREATE FULLTEXT CATALOG EmployeeCatelog;  
    GO  
      
    CREATE FULLTEXT INDEX ON dbo.EmployeeDetails   
     (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails  
     ON EmployeeCatelog;  
     -- By default CHANGE_TRACKING = AUTO  
      
      
    -- Once enabled, search can be performed;  
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');  
      
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((Resume), 'SQL');  
      
    -- Get the rank and sort the result using it  
    SELECT t.Rank, e.*  
    FROM dbo.EmployeeDetails e  
     INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t  
      ON e.EmployeeDetailsId = t.[Key]  
    ORDER BY t.Rank DESC  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

There are times when we need to pass a SQL query as a string that has been created dynamically and execute it on database or from the code. For this purpose, we can use a built-in stored procedure, sp_executesql.

Stored procedure, sp_executesql executes a SQL statement or batch that can be reused many times, or one that has been built dynamically.

Here is the syntax:
sp_executesql [ @stmt = ] statement 
[ 
{ , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } 
{ , [ @param1 = ] 'value1' [ ,...n ] } 
] 

Here is an example where SQL is compiled as a string, str1. The SELECT SQL statement is executed via the string parameter passed to the sp_executesql.
declare @str1 nvarchar(200) -----declare a variable 
set @str1='SELECT * FROM tablename' --- set your query to variable 
exec sp_executesql @str1 

Here we will explain how to find the Indian financial year and financial quarter in a particular date with an example in SQL Server.  I have used the CASE statement and DATEPART() function to achieve this requirement.

DATEPART() in SQL Server
The DATEPART() function returns a specified part of a date, like – year, month, day, hour, minute, etc.

CASE Statement in SQL Server
CASE is the extension of IF ... ELSE statement. So, once a condition is true, it will stop reading & return the result. If no conditions are true, it returns the value in the ELSE block.
 
A) Find the FINANCIAL YEAR from date
 Write the below-given SQL code to find the financial year from given particular date,
    DECLARE@FilterDateASDATETIME 
    SET@FilterDate = GETDATE() 
    SELECTCASEWHENDATEPART(QUARTER,@FilterDate)= 1 THENYEAR(@FilterDate)ELSEYEAR(@FilterDate)+ 1 ENDAS[FINANCIAL_YEAR] 
    ** Note - @FilterDate - The date to be find the financial year

Output
Following is the result of the SQL query for financial year,

 
B) Find the FINANCIAL QUARTER from date
 
Write the below SQL code to find the financial Quarter from given particular date,
    DECLARE@FilterDateASDATETIME 
    SET@FilterDate = GETDATE() 
    SELECTCASEDATEPART(QUARTER, @FilterDate)WHEN 1 THEN'Q4'WHEN 2 THEN'Q1'WHEN 3 THEN'Q2'WHEN 4 THEN'Q3'ENDAS[FINANCIAL_QUARTER] 
    ** Note - @FilterDate - The date to be find the financial Quarter

Output
Following is the result of the SQL query for the financial quarter,

In this post, I'll share few useful SQL queries for database analysis on SQL Server 2012. I shared few SQL queries useful in analyzing database, which I use quite often. This query will return all table names and no.of rows in it for built-in tables.

    -- List all table names and number of rows in it for user-defined tables 
    SELECT distinct t.name,prt.rows 
    FROM sys.tables t INNER JOIN sys.partitions AS prt 
    ON t.object_id = prt.object_id where t.is_ms_shipped=1 -- 0 for user-defined tables 
    order by prt.rows desc 

This query will return column names and its data type of a table.
    -- Get column names and its types of a table 
    SELECT cols.name,t.name 
    FROM sys.objects o join sys.columns cols on o.object_id= cols.object_id 
    join sys.types t on t.system_type_id=cols.system_type_id 
    and o.name='Employee'-- Table Name

This query will return file name, its size and file group name of a database.
    SELECT sdf.name AS [FileName], 
    size/128 AS [Size], 
    fg.name AS [File_Group_Name] 
    FROM sys.database_files sdf 
    INNER JOIN 
    sys.filegroups fg 
    ON sdf.data_space_id=fg.data_space_id 

Batch file to execute all sql files in a directory, Save it as .bat in a folder that have sql script files to be executed.
    @Echo Off 
    FOR /f %%i IN ('DIR *.Sql /B') do call :RunSql %%i 
    GOTO :END 
    :RunSql 
    Echo Executing SQL: %1 
    SQLCMD -S server1 -U user1 -P pwd1 -d DB1 -i %1 
    Echo Completed SQL: %1 
    :END 

This query will return all table names that have a Foreign key:
    SELECT SCHEMA_NAME(schema_id) AS SchemaName, 
    name AS TableName 
    FROM sys.tables where OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 1 -- Return all

HostForLIFE.eu SQL Server 2012 Hosting
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.

Database Administrators usually use the sp_renamedb system stored procedure to quickly rename a SQL Server Database. However, the drawback of using sp_renamedb is that it doesn't rename the Logical and Physical names of the underlying database files. It's a best practice to make sure the Logical Name and Physical File Name of the database is also renamed to reflect the actual name of the database to avoid any confusion with backup, restore or detach/attach operations.

Let's first create a new database named CoreDB using the T-SQL below:

USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CoreDB')
DROP DATABASE CoreDB
GO
USE master
GO
CREATE DATABASE [CoreDB]
ON PRIMARY
(
NAME = N'CoreDB',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf' ,
SIZE = 2048KB ,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'CoreDB_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 10%
)
GO
Rename CoreDB Database Using sp_renamedb System Stored Procedure

Now let's rename the CoreDB database to ProductsDB by executing the below T-SQL code.

USE master
GO
ALTER DATABASE CoreDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'CoreDB','ProductsDB'
GO
ALTER DATABASE ProductsDB
SET MULTI_USER
GO
Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')
GO
Your output should look something like this from the above query.

You can see in the above snippet that the Logical Name and File Name in the DB File Path column for ProductsDB are still reflecting the old name of CoreDB. This is not a good practice to follow in a Production Environment. Below you will see the steps which a DBA can follow to rename the database and its respective files.

Steps to Rename a SQL Server Database

DBAs should follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database.

This first set of commands put the database in single user mode and also modifies the logical names.


/* Set Database as a Single User */
ALTER DATABASE CoreDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB', NEWNAME=N'ProductsDB')
GO
ALTER DATABASE [CoreDB] MODIFY FILE (NAME=N'CoreDB_log', NEWNAME=N'ProductsDB_log')
GO
This is the output from the above code.

Now we need to detach the database, so we can rename the physical files.  If the database files are open you will not be able to rename the files.

/* Detach Current Database */
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'CoreDB'
GO
Once the CoreDB database is detached successfully then the next step will be to rename the Physical Files. This can be done either manually or by using the xp_cmdshell system stored procedure. You can enable xp_cmdshell feature using the sp_configure system stored procedure.

USE master
GO
sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.

/* Rename Physical Files */
USE [master]
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\Program Files\Microsoft SQL ServerMSSQL10.SQL2008\
MSSQL\DATA\CoreDB_log.ldf", "ProductsDB_log.ldf"'
GO
Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:

/* Attach Renamed ProductsDB Database Online */
USE [master]
GO
CREATE DATABASE ProductsDB ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf' )
FOR ATTACH
GO
Once the above step has successfully executed then the final step will be to allow multi user access for the user database by executing the below T-SQL:

/* Set Database to Multi User*/
ALTER DATABASE ProductsDB SET MULTI_USER
GO
You can verify the Logical and File Names for the ProductsDB database by executing the T-SQL below:

USE master
GO
/* Identify Database File Names */
SELECT
name AS [Logical Name],
physical_name AS [DB File Path],
type_desc AS [File Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'ProductsDB')

HostForLIFE.eu SQL Server 2012 Hosting
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 customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

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.

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] 

In this article, I described how to pass a table to a function parameter in SQL Server. In this article, you create a table, student, then create a user-defined table type and pass the table type as a parameter to a function. So let's have a look at a practical example of how to pass a table as a function parameter in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Here is how to implement passing a user-defined table type to a function.

1. Create a Student Table in SQL Server
Create a table named Student.
CREATE TABLE [dbo].[Student] 
( 
    [StudentID] [int] NULL, 
    [StudentName] [varchar](30) NULL, 
    [StudentFees] [int] NULL 
) 

2. Create a User-Defined Table Type in SQL Server
Now create a user-defined table type to be used as a table-valued parameter in the function.
CREATE TYPE dbo.StudentType AS TABLE 
( 
   [StudentID] [int] , 
   [StudentName] [varchar](30) , 
   [StudentFees] [int]  
) 

Now Press F8 to see the created type in the Object Explorer.
Database->Programmability->Types->User Define Table Types

3. Creating a Function in SQL Server
Now create the StudentDetailFunctionFunction. This function will accept a table-valued parameter.
READONLY keyword - This keyword is required to declare a table-valued parameter.

ALTER FUNCTION StudentDetailFunction( @StudentDetail dbo.StudentType READONLY ) 
RETURNS VARCHAR(50) 
AS 
BEGIN 
    DECLARE @Studentname VARCHAR(50) 
    SELECT  @Studentname= StudentName FROM @StudentDetail 
    RETURN @Studentname 
END 

4. Execute the SQL Server Function
Now you can declare a variable @StudentVariable which contains the value of the table columns.
DECLARE @StudentVariable AS StudentType 
INSERT INTO @StudentVariable(StudentName) VALUES('Peter') 
SELECT dbo.StudentDetailFunction(@StudentVariable) 

Definition of sp_MSforeachtable procedure in SQL Server

Use Testdb  
exec sp_MSforeachtable 'print "?"' 

Another example. The following script helps us to determine the space used and allocated for every table in the database.

Example script

 Use Testdb 
    exec sp_MSforeachtable 'EXECUTE sp_spaceused [?];' 

Common uses of sp_MSforeachtable Stored
ProcedureThis stored produce may be used for the following purposes.

• To get the size of all the tables in the database
• To rebuild all indexes of all the tables in the database
• Disable all constraints and triggers of all the tables in the database
• Delete all the data from all the tables in the database
• To RESEED all tables to 0
• To get the Number of Rows in all tables in a database
• Update the statistics of all the tables in a database
• Reclaim space from dropped variable-length columns in tables or indexed views of the database

These undocumented Stored Procedures can be used if we want to do the same operation on each table of any database. Please note that Microsoft may change the functionality and definition of this Stored Procedure at any time.

HostForLIFE.eu SQL Server 2016 Hosting

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.

This article explains schemas in SQL Server. A SQL schema in a database is a collection of logical structures of data. A schema in a database is a collection of logical structures of data. The schema is owned by a database user and is the same name as the database user. From SQL Server 2005, a schema is an independent entity (container of objects) different from the user who creates that object. In other words, schemas are very similar to separate namespaces or containers that are used to store database objects. Security permissions can be applied to schemas hence schemas are an important tool for separating and protecting database objects on the basis of user access rights. It improves flexibility for security-related administration of the database.

User schema separation
Before SQL Server 2005, database object owners and users were the same things and database objects (table, index, view and so on) were owned by the user. In other words database objects were directly linked to the user and the user could not delete them without removing the database object that were associated with the user. In SQL Server 2005, a schema separation is introduced, now the database object is no longer owned by a user, group or role. The schema can be owned by the user, group or role. The schema can have multiple owners. The schema ownership is transferrable. Database objects are created within the schema. Now the user can be dropped without the dropping of the database object owned by the user. But the schema cannot be deleted if it contains a database object.

The following are advantages of user schema separation:

The schema ownership is transferrable.
Database objects can be moved among the schemas.
A single schema can be shared among multiple users.
A user can be dropped without dropping the database objects associated with the user.
Provides more control of access and level of access.

Default schema
The default schema is the first schema searched when resolving object names. The user can be defined within the default schema. Using the "SCHEMA_NAME" function we can determine the default schema for the database.

The schema can be made the default for the user by defining DEFAULT_SCHEMA with CREATE USER or ALTER USER. If there is no default schema defined then SQL will assume "DBO" as the default schema. Note that there is no default schema associated with a user if the user is authenticated as a member of the group in the Windows operating system. In this case a new schema will be created and the name is the same as the user name.

Advantages of using Schema
Act as object protection tool: A schema can be a very effective object projection tool combined with the appropriate level of user permissions. A DBA can maintain control access to an object that would be very crucial.

Managing a logical group of database objects within a database: Schemas allow database objects to be organized into a logical group. This would be advantagous when multiple teams are working on the same database application and the design team wants to maintain integrity of the database tables.

Easy to maintain the database: A schema allows a logical grouping of the database objects, so the schema can help us in situations where the database object name is the same but falls in a different logical group.

Other Advantages
A single schema can be shared among multiple databases and database users.
A database user can be dropped without dropping database objects.
Manipulation of and access to the object is now very complex and more secure. The schema acts as an additional layer of security.
Database objects can be moved among schemas.
The ownership of schemas is transferable.

A schema is a very useful database concept and helps us to separate database users from the database object owners and also helps to create a logical grouping of database objects.