Friday 28 October 2016

Find the table created date in sql server

Suppose we want to find the date when the table is created. We below sql script we will get the created and updated date of the table.
use AdventureWorks2012
SELECT
        [name] as [Table Name],
                                type_desc as [Table Type],
        create_date as [Created Date],
        modify_date as [Modify Date]
FROM
        sys.tables
order by create_date desc

All information is store in sys.table table

See the Output.

Find the Column Name in a table or View in sql

With the help of below sql script we can find the column name and data type of the column in table or view.
use AdventureWorks2012
DECLARE @columnname VARCHAR(25)
SET @columnname = 'Employee'--enter the search column name

SELECT SCHEMA_NAME(t.schema_id) AS [Schema Name]
       ,t.NAME AS [Table or View Name]
       ,c.NAME AS [Column Name]
       ,dt.NAME AS [Data Type]
       ,c.max_length as size
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types dt ON c.system_type_id = dt.system_type_id
WHERE c.NAME LIKE '%' + @columnname + '%'
      AND dt.NAME <> 'sysname'
 UNION
 --Get the same from all the views
SELECT SCHEMA_NAME(v.schema_id) AS [Schema Name]
       ,v.NAME AS [Table or View Name]
       ,c.NAME AS [Column Name]
       ,dt.NAME AS [Data Type]
       ,c.max_length AS Size
FROM sys.VIEWS AS v
INNER JOIN sys.columns c ON v.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types dt ON c.system_type_id = dt.system_type_id
WHERE c.NAME LIKE '%' + @columnname + '%'
      AND dt.NAME <> 'sysname'

Here I am searching a column name which having column name like ‘Employee’.
See the Output

Let’s see the column name in table
  

Similarly we can see it in view also.

Find the Given text in sp in sql server

With the help of below sql script we can find the given text in SP.
SELECT
 OBJECT_NAME(object_id) as [SP Name],
  OBJECT_DEFINITION(object_id) as [desc]
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%ProductAssemblyID%'
See the output

In both SP have this Text.



Find the triggers in database in sql server

With the help of below sql script we will get the list of trigger in a database
use AdventureWorks2012
SELECT
       tbl.name as [Table Name]
     , trig.name as [Trigger Name]
     , trig.is_disabled
FROM [sys].[triggers] as trig
INNER JOIN sys.tables as tbl
ON trig.parent_id = tbl.object_id


See the example

Re-name a Table in Sql Server

We can rename the table name with the help of SP_RENAME SP. Below is syntax
SP_RENAME  'old table name', 'New table name'
See the example

Just I am Renaming the Emp table.
SP_RENAME  'dbo.Emp', 'dbo.Emp_Rename'
  
 Now name has been changed

Monday 24 October 2016

Get the count of Tables, SP, Function or Views exist in Sql server

With the help of below sql script we will get the count of tables, sp, function and views in each database on the server.

Declare @temp table
(ID int identity(1,1),
Name varchar(50))
insert into @temp
select name from master.sys.Databases

declare @min int=1 , @max int
set @max=(select Count(*) from @temp)
declare @Counttbl table
(ID int identity(1,1), DatabaseName varchar(50),
TableCount int,ViewCount int, functionCount int,SPCount int)

while (@min<=@max)
Begin

declare @dbName varchar(50)
declare @s varchar(1000)
set @dbName=(select Name from @temp where id=@Min)

--insert into @Counttbl
set @s= 'Use ' + @dbName +'
SELECT ''
'+@dbName+' ''as DatabaseName,
COUNT(*) AS TABLE_COUNT ,
(SELECT COUNT(*) FROM  INFORMATION_SCHEMA.VIEWS) as Views,
(SELECT COUNT(*)  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''FUNCTION'' ) AS FUNCTION_COUNT,
(SELECT  COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'') AS PROCEDURE_COUNT
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'''
insert into @Counttbl
exec(@s)
set @min=@min+1
End
select * from @Counttbl

 

Sunday 23 October 2016

Get the active connection of SQL-Server

With the help of below sql script we will get the active connection of the server.
SELECT Session.program_name as [Program Name],
Session.login_name as [Login Name],
Session.host_name as [Host name],
COUNT(Session.session_id) [Connections]
FROM sys.dm_exec_sessions Session
INNER JOIN sys.dm_exec_connections Conn
ON Session.session_id = Conn.session_id
WHERE Session.is_user_process = 1
GROUP BY Session.program_name,
Session.login_name,
Session.host_name
HAVING COUNT(Session.session_id) > 2
ORDER BY COUNT(Session.session_id) desc

See the example


Currently I have 4 connections

Friday 14 October 2016

Periodic Restart Production Server using sql script

Suppose I want to restart my server on every Sunday mid night. We can use below sql script and schedule it on sql server agent for mid night.
USE master
go
 -- On Sunday Executes
IF (DATEPART(DW,GETDATE()) = 1)
BEGIN
EXEC Master.dbo.Sp_Configure 'Show Advanced Options', 1
RECONFIGURE
EXEC Master.dbo.Sp_Configure 'XP_CmdShell', 1
RECONFIGURE

EXEC Master.dbo.xp_cmdshell 'C:\WINDOWS\system32\shutdown.exe /r'

EXEC Master.dbo.Sp_Configure 'Show Advanced Options', 1
RECONFIGURE
EXEC Master.dbo.Sp_Configure 'XP_CmdShell', 0
RECONFIGURE
END
With the help of DATEPART Function we are finding the day.
DATEPART (dw,@Date) will return the numeric value.
Day
Numeric value
Sunday
1
Monday
2
Tuesday
3
Wednesday
4
Thursday
5
Friday
6
Saturday
7
Now I am running this script. We will get below
  
After 1 min server will be restart

  

Wednesday 12 October 2016

Allocation unit in sql server

When we are creating a table in sql server, it creates a whole hierarchy Object, partition and Allocation units. Unit store the data type.

There are 3 types of allocation units in sql server
Ø  In Row Allocation unit : it store the fix length data type like (char, int, float , date time etc)
Ø  Row Overflows Allocation Unit: it store the variable length data type like (nvarchar, varchar etc)
Ø  Lob Data Allocation Unit : it store the lob data type like (ntext, xml, Image etc)

Let’s see the example

Here I am creating a table and see that what happen inside the database.
create table tblAllocationUnitTest
(ID int,
FName char(200),
LName char(200),
Mob Char(200))
Once we create the table it store in the sys.object and at least it create a partition.
select * from sys.objects
where object_id=885578193
  

It must create a partition
select * from sys.partitions
where object_id=885578193
   

We know that data types are store in units. We can see it on the sys.allocation_units
select * from sys.allocation_units
where container_id=72057594039304192 --it is partition_id
  
Currently in the table we have only fix length data type.
Now I am adding the variable data type in the table
Alter table tblAllocationUnitTest Add eAdd varchar(500)
  
Now I am adding the LOB data type.
Alter table tblAllocationUnitTest Add ad varchar(max)
 


Popular Posts