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

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

 

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

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

  

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)