Dynamic generating insert script with given condition and with only required column for given table

Hi Folks,

Today I am going to share a script by which we can dynamically create insert script with only needed columns and only needed data.
In simple words we can avoid unwanted data, even we can avoid unwanted column from insert list.

Here is example table and insert data:
***** you can directly use script at any point or any situation, it is always safe and did not harm any data or any definition  *****

CREATE TABLE [dbo].[Calender](
    [years] [varchar](5) NULL,
    [MONTHS] [varchar](5) NULL,
    [salesAmount] [int] NULL,
    [avrgsaleperMonth] [float] NULL
)

GO

INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jan’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’FEB’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Mar’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Apr’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’May’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jun’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Jul’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Aug’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’Sep’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’OCT’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’NOV’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2000′,[MONTHS]=’DEC’,[salesAmount]=’20’,[avrgsaleperMonth]=’11.36′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jan’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’FEB’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Mar’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Apr’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’May’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jun’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Jul’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Aug’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’Sep’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’OCT’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’NOV’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2001′,[MONTHS]=’DEC’,[salesAmount]=’30’,[avrgsaleperMonth]=’52.69′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jan’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’FEB’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Mar’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Apr’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’May’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jun’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Jul’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Aug’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’Sep’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’OCT’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’NOV’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2002′,[MONTHS]=’DEC’,[salesAmount]=’40’,[avrgsaleperMonth]=’31.22′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jan’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’FEB’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Mar’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Apr’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’May’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jun’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Jul’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Aug’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’Sep’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’OCT’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’NOV’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2003′,[MONTHS]=’DEC’,[salesAmount]=’50’,[avrgsaleperMonth]=’9.87′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jan’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’FEB’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Mar’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Apr’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’May’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jun’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Jul’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Aug’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’Sep’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’OCT’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’NOV’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′
INSERT INTO [dbo].[Calender]
([years],[MONTHS],[salesAmount],[avrgsaleperMonth])
SELECT [years]=’2004′,[MONTHS]=’DEC’,[salesAmount]=’60’,[avrgsaleperMonth]=’2.01′

Now this is the script for generate script of what we want excluding all unwanted column and with only required as we can pass these values in our parameters(@NonIncludeColumnName, @Condition)
as describe below just execute the script and you got the insert script of newly created table (Calender) with only where month = Jan and AvrgSalePerMonth is not null,
And with exclude the column AvrgSalePerMonth and month.

Script :

DECLARE
/***** TableName ****/
@TableName SYSNAME =’Calender’
/***** SchemaName ****/
,@SchemaName SYSNAME =’dbo’ 
/****** JUST PASS COMMA SEPRATED VALUES OF ALL DON’T WANT TO INCLUDE  COLUMN ********/
,@NonIncludeColumnName varchar(250) = ‘avrgsaleperMonth,months’
/***** Where Condition/left blank(”) if don’t want any filtering  ****/
,@Condition VARCHAR(500) = ‘[MONTHS]=”Jan” and avrgsaleperMonth is not null’

    DECLARE @ColumnList VARCHAR(max)
    DECLARE @InsertList VARCHAR(max)
    DECLARE @pkList VARCHAR(max)
    DECLARE @SQL varchar(max)
    DECLARE @WhereSQL varchar(max)
    SELECT @ColumnList = ”
    SELECT @InsertList = ”
    SELECT @pkList  = ” 

/************** Collect NonIncluding Columns **************************/
DECLARE @delimiter nchar(1) = ‘,’

DECLARE @result TABLE
    ([id] int NOT NULL
    ,[value] nvarchar(4000))

IF ISNULL(@NonIncludeColumnName, ”) = N” OR ISNULL(@delimiter, N”) = N”
       OR ISNULL(CHARINDEX(@delimiter, @NonIncludeColumnName, 1), 0) = 0
    BEGIN
        — No valid delimiter and/or csv, so return only the csv value as it.
        INSERT INTO @result ([id], [value]) VALUES (1, @NonIncludeColumnName);
        RETURN;
    END;
    — Csv must end with the delimiter:
    IF LEN(@NonIncludeColumnName) < 4000 AND RIGHT(@NonIncludeColumnName, 1) <> @delimiter
        SET @NonIncludeColumnName = @NonIncludeColumnName + @delimiter;
    — Split the csv value with a CTE
    WITH cte ([id], [value], [leftCsv])
    AS (SELECT 1 as [id]
              ,SUBSTRING(@NonIncludeColumnName
                        ,1
                        ,CHARINDEX(@delimiter, @NonIncludeColumnName, 1) – 1) AS [value]
              ,SUBSTRING(@NonIncludeColumnName
                        ,CHARINDEX(@delimiter, @NonIncludeColumnName, 1) + 1
                        ,LEN(@NonIncludeColumnName)) AS [leftCsv]
        UNION ALL
        SELECT cte.[id] + 1 AS [id]
              ,SUBSTRING(cte.[leftCsv]
                        ,1
                        ,CHARINDEX(@delimiter, cte.[leftCsv], 1) – 1) AS [value]
              ,SUBSTRING(cte.[leftCsv]
                        ,CHARINDEX(@delimiter, cte.[leftCsv], 1) + 1
                        ,LEN(cte.[leftCsv])) AS [leftCsv]
        FROM cte
        WHERE CHARINDEX(@delimiter, cte.[leftCsv], 1) <> 0)
    INSERT INTO @result ([id], [value])
    SELECT [id], [value]
    FROM cte
    OPTION (MAXRECURSION 4000);

/************** END of Collect NonIncluding Columns **********************/
/**************  Collect all column name for the table ******************/
    SELECT @ColumnList = @ColumnList + CASE 
                                       WHEN ST.name not in (‘timestamp’,’geography’,’geometry’,’hierarchyid’,’image’,’binary’,’varbinary’)
                                       THEN Quotename(SC.name) + ‘,’ 
                                       ELSE ” 
                                       END 
     — Collect all insert statments for the table                                 
          ,@InsertList = @InsertList + CASE WHEN ST.name not in (‘timestamp’,’geography’,’geometry’,’hierarchyid’,’image’,’binary’,’varbinary’) THEN
                                        ”” +  QUOTENAME(sc.name) + ‘=” + ‘  + ‘CASE WHEN ‘ + Quotename(SC.name) 
                                        + ‘ IS NULL THEN ”NULL” ELSE  ”””” +    REPLACE(CONVERT(VARCHAR(MAX),’ + Quotename(SC.name) + ‘),””””,””””””) + ””””  END + ‘ + ‘ ”,” + ‘
                                       ELSE ”
                                       END
      FROM SYS.objects so
      JOIN SYS.columns sc
        ON so.object_id = sc.object_id
      JOIN SYS.types st
        ON sc.user_type_id = st.user_type_id
       AND sc.system_type_id = st.system_type_id
      JOIN SYS.schemas sch
        ON sch.schema_id = so.schema_id
     WHERE SO.name = @TableName 
       AND sch.name = @SchemaName
       AND sc.name  IN ( SELECT Value FROM @result )    /********************Which Column Need To Remove**********************/
     ORDER BY SC.column_id
/******************** ADD WHERE CONDITION **********************/
    SELECT @WhereSQL = CASE 
                       WHEN LTRIM(RTRIM(@Condition)) <> ” THEN ‘ WHERE ‘ + @Condition
                       ELSE ”
                       END
    SELECT @ColumnList =SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1)
    SELECT @InsertList =SUBSTRING(@InsertList,1,LEN(@InsertList)-4) + ””
   — combine all variable and generate the final list
    SELECT @SQL = ‘SELECT ”INSERT INTO ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@Tablename) +  char(10)+  ‘(‘ + @ColumnList + ‘)” + char(10)+  ”SELECT ” + ‘ + @InsertList 
                + ‘ FROM ‘ + QUOTENAME(@SchemaName) + ‘.’ + QUOTENAME(@Tablename) 
                + @WhereSQL
/******************** EXECUTE THE STATEMENT **********************/
     –PRINT @sql
    EXEC(@SQL)
/******************** END OF SCRIPT **********************/
For removing the table. from database just execute below given drop table statement.
Drop Table TABLE [dbo].[Calender]

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Advertisements

All Table’s full information captured in self created tables

Hi Folks,

once upon a time for some kind of optimization, table definition change tracking ,constraints review and for deep dive into all table information of a database, come up with the solution of creating a procedure for fully covered table information including all Columns , all Constraint, belonging FileGroup, Identity_Col of that table, all Indices, owner of that table, foreign key columns and many more information is covered and represent within from database tables within one execution, because system created table is not always shows full information of all table in single query.

On the way of final result we have to cross some simple stages :

For this first we need to create tables for store all information :

Just Execute the below script for creating Schema and Tables

NOTE : it’ll surely not harmful for any database or server but Do not use on production server. this script will create schema and tables and procedure which causes the definition of a database might change.
Schema & Table definition:

CREATE SCHEMA info
GO

***************************TABLE CREATE**********************************

/****** Object:  Table [info].[Table_RefrenceBy_col]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Table_RefrenceBy_col](
    [TableName] [varchar](60) NULL,
    [Table_RefrencedBy_Col] [nvarchar](516) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[RowGUID]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[RowGUID](
    [TableName] [varchar](60) NULL,
    [RowGuidCol] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[owner]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[owner](
    [TableName] [varchar](60) NULL,
    [Name] [varchar](40) NULL,
    [Owner] [varchar](20) NULL,
    [Type] [varchar](30) NULL,
    [Created_datetime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[Index]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Index](
    [TableName] [varchar](60) NULL,
    [index_name] [sysname] NOT NULL,
    [Index_description] [varchar](max) NULL,
    [index_keys] [nvarchar](2078) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[Identity_Col]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Identity_Col](
    [TableName] [nvarchar](60) NULL,
    [Identity] [nvarchar](128) NULL,
    [Seed] [numeric](18, 0) NULL,
    [Increment] [numeric](18, 0) NULL,
    [Not For Replication] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[FileGroup]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[FileGroup](
    [TableName] [varchar](60) NULL,
    [Data_located_on_filegroup] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[Constraint]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Constraint](
    [TableName] [varchar](60) NULL,
    [constraint_type] [nvarchar](146) NULL,
    [constraint_name] [nvarchar](MAX) NULL,
    [delete_action] [nvarchar](9) NULL,
    [update_action] [nvarchar](9) NULL,
    [status_enabled] [varchar](8) NULL,
    [status_for_replication] [varchar](19) NULL,
    [constraint_keys] [nvarchar](MAX) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Table [info].[Columns]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [info].[Columns](
    [TableName] [varchar](60) NULL,
    [Column_name] [nvarchar](128) NULL,
    [Type] [nvarchar](128) NULL,
    [Computed] [varchar](35) NULL,
    [Length] [int] NULL,
    [Prec] [char](8) NULL,
    [Scale] [char](5) NULL,
    [Nullable] [varchar](35) NULL,
    [TrimTrailingBlanks] [varchar](35) NULL,
    [FixedLenNullInSource] [varchar](35) NULL,
    [Collation] [sysname] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO

Now you have all tables just need to create procedure and execute,

Procedure definition:

CREATE PROC Info_Table
AS
BEGIN
DECLARE @cnt INT , @loopCnt INT = 1–,@DataBaseName NVARCHAR(776)= DB_NAME()
select @cnt = count(*) from information_schema.tables
DECLARE @objname NVARCHAR(776)
WHILE @loopCnt <=  @cnt
BEGIN
IF OBJECT_ID(‘tempdb..#Objects’) IS NOT NULL
    BEGIN
        DROP TABLE #Objects
    END
select identity (INT,1,1) AS ID , TABLE_SCHEMA+’.’+TABLE_NAME As Tablename INTO #Objects from information_schema.tables
SELECT @objname = Tablename   FROM #objects where ID = @loopCnt

DECLARE @dbname SYSNAME
    ,@no VARCHAR(35)
    ,@yes VARCHAR(35)
    ,@none VARCHAR(35)

SELECT @no = ‘no’
    ,@yes = ‘yes’
    ,@none = ‘none’

DECLARE @Results TABLE (
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
    ,ResultsText VARCHAR(8000)
    )

SELECT @dbname = PARSENAME(@objname, 3)

IF @dbname IS NULL
    SELECT @dbname = DB_NAME()
ELSE
    IF @dbname <> DB_NAME()
    BEGIN
        INSERT INTO @Results (ResultsText)
        SELECT ‘the @objname must be local to the current database, cross database queries or 3 part naming conventions are not permitted’

        SELECT *
        FROM @Results
    END

DECLARE @objid INT
DECLARE @sysobj_type CHAR(2)

SELECT @objid = object_id
    ,@sysobj_type = type
FROM sys.all_objects
WHERE object_id = OBJECT_ID(@objname)

— IF NOT IN SYSOBJECTS, TRY SYSTYPES — 
IF @objid IS NULL
BEGIN
    — UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME 
    SELECT @objid = TYPE_ID(@objname)

    — IF NOT IN SYSTYPES, GIVE UP 
    IF @objid IS NULL
    BEGIN
        INSERT INTO @Results (ResultsText)
        SELECT ‘the object_id for ‘ + @objname + ‘ was not found’

        SELECT *
        FROM @Results
    END
END

DELETE
FROM info.[Owner]
WHERE TableName = @objname

INSERT INTO info.OWNER
SELECT @objname TableName
    ,convert(VARCHAR(20), o.NAME) NAME
    ,convert(VARCHAR(20), USER_NAME(OBJECTPROPERTY(object_id, ‘ownerid’))) ‘Owner’
    ,convert(VARCHAR(20), SUBSTRING(v.NAME, 5, 31)) ‘Type’
    ,convert(DATETIME, o.create_date) Created_datetime
FROM sys.all_objects o
    ,master.dbo.spt_values v
WHERE o.object_id = object_id(@objname)
    AND o.type = SUBSTRING(v.NAME, 1, 2) COLLATE database_default
    AND v.type = ‘O9T’
ORDER BY o.NAME ASC

IF EXISTS (
        SELECT *
        FROM sys.all_columns
        WHERE object_id = @objid
        )
BEGIN
    — SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE 
    DECLARE @numtypes NVARCHAR(80)

    SELECT @numtypes = N’tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney’

    DELETE
    FROM info.Columns
    WHERE TableName = @objname

    INSERT INTO info.COlumns
    SELECT @objname
        ,CONVERT(CHAR(50), NAME)
        ,CONVERT(CHAR(16), TYPE_NAME(user_type_id))
        ,CONVERT(CHAR(8), CASE
                WHEN COLUMNPROPERTY(object_id, NAME, ‘IsComputed’) = 0
                    THEN @no
                ELSE @yes
                END)
        ,CONVERT(CHAR(8), CONVERT(INT, max_length))
        ,CONVERT(CHAR(8), CASE
                WHEN CHARINDEX(TYPE_NAME(system_type_id), @numtypes) > 0
                    THEN CONVERT(CHAR(5), COLUMNPROPERTY(object_id, NAME, ‘precision’))
                ELSE ‘     ‘
                END)
        ,CONVERT(CHAR(8), CASE
                WHEN CHARINDEX(TYPE_NAME(system_type_id), @numtypes) > 0
                    THEN CONVERT(CHAR(5), ODBCSCALE(system_type_id, scale))
                ELSE ‘     ‘
                END)
        ,CONVERT(CHAR(8), CASE
                WHEN is_nullable = 0
                    THEN @no
                ELSE @yes
                END)
        ,CONVERT(CHAR(18), CASE COLUMNPROPERTY(object_id, NAME, ‘UsesAnsiTrim’)
                WHEN 1
                    THEN @no
                WHEN 0
                    THEN @yes
                ELSE ‘(n/a)’
                END)
        ,CONVERT(CHAR(20), CASE
                WHEN TYPE_NAME(system_type_id) NOT IN (
                        ‘varbinary’
                        ,’varchar’
                        ,’binary’
                        ,’char’
                        )
                    THEN ‘(n/a)’
                WHEN is_nullable = 0
                    THEN @no
                ELSE @yes
                END)
        ,CONVERT(CHAR(20), ISNULL(collation_name, ‘NULL’))
    FROM sys.all_columns
    WHERE object_id = object_id(@objname)
END –if exists sys.columns   

IF @sysobj_type IN (
        ‘S ‘
        ,’U ‘
        ,’V ‘
        ,’TF’
        )
    AND @objid > 0
BEGIN
    DECLARE @colname SYSNAME

    SELECT @colname = COL_NAME(@objid, column_id)
    FROM sys.identity_columns
    WHERE object_id = @objid

    DELETE
    FROM info.Identity_Col
    WHERE TableName = @objname

    INSERT INTO info.Identity_Col
    SELECT @objname
        ,CONVERT(CHAR(27), ISNULL(@colname, ‘No identity column defined.’))
        ,CONVERT(CHAR(4), IDENT_SEED(@objname))
        ,CONVERT(CHAR(9), IDENT_INCR(@objname))
        ,CONVERT(CHAR(20), COLUMNPROPERTY(@objid, @colname, ‘IsIDNotForRepl’))

    SELECT @colname = NULL

    SELECT @colname = NAME
    FROM sys.columns
    WHERE object_id = @objid
        AND is_rowguidcol = 1

    DELETE
    FROM info.RowGUID
    WHERE TableName = @objname

    INSERT INTO info.RowGUID
    SELECT @objname
        ,CONVERT(CHAR(30), ISNULL(@colname, ‘No rowguidcol column defined.’))

    IF EXISTS (
            SELECT *
            FROM sys.all_parameters
            WHERE object_id = @objid
            )
    BEGIN
        DROP TABLE #TEMP

        SELECT CONVERT(CHAR(50), NAME) Param_Name
            ,CONVERT(CHAR(16), TYPE_NAME(user_type_id)) user_type_id
            ,CONVERT(CHAR(8), max_length) max_length
            ,CONVERT(CHAR(16), CASE
                    WHEN TYPE_NAME(system_type_id) = ‘uniqueidentifier’
                        THEN PRECISION
                    ELSE ODBCPREC(system_type_id, max_length, PRECISION)
                    END) system_type_id
            ,CONVERT(CHAR(16), ISNULL(ODBCSCALE(system_type_id, scale), ”)) scale
            ,CONVERT(CHAR(16), parameter_id) parameter_id
            ,CONVERT(CHAR(50), CONVERT(SYSNAME, CASE
                        WHEN system_type_id IN (
                                35
                                ,99
                                ,167
                                ,175
                                ,231
                                ,239
                                )
                            THEN SERVERPROPERTY(‘collation’)
                        ELSE ”
                        END)) collation
        INTO #temp
        FROM sys.all_parameters
        WHERE object_id = @objid

        SELECT *
        FROM #TEMP
    END
END

IF @sysobj_type IN (
        ‘S ‘
        ,’U ‘
        )
BEGIN
    DELETE
    FROM info.FileGroup
    WHERE TableName = @objname

    IF OBJECT_ID(‘tempdb..#temp2’) IS NOT NULL
    BEGIN
        DROP TABLE #Temp2
    END

    CREATE TABLE #Temp2 (FG VARCHAR(max))

    INSERT INTO #Temp2
    EXEC sys.sp_objectfilegroup @objid

    INSERT INTO info.FileGroup
    SELECT @objname
        ,FG
    FROM #Temp2

    DELETE
    FROM info.[Index]
    WHERE TableName = @objname

    IF OBJECT_ID(‘tempdb..#temp3’) IS NOT NULL
    BEGIN
        DROP TABLE #Temp3
    END

    CREATE TABLE #Temp3 (
        IND_Name SYSNAME
        ,Descr VARCHAR(max)
        ,Keys NVARCHAR(2076)
        )

    INSERT INTO #TEMP3
    EXEC sys.sp_helpindex @objname

    INSERT INTO info.[Index]
    SELECT @objname
        ,*
    FROM #TEMP3

    DELETE
    FROM info.Table_RefrenceBy_col
    WHERE TableName = @objname

    IF EXISTS (
            SELECT *
            FROM sys.foreign_keys
            WHERE referenced_object_id = @objid
            )
        INSERT INTO info.Table_RefrenceBy_col
        SELECT @objname
            ,db_name() + ‘.’ + rtrim(schema_name(ObjectProperty(parent_object_id, ‘schemaid’))) + ‘.’ + object_name(parent_object_id) + ‘: ‘ + object_name(object_id)
        FROM sys.foreign_keys
        WHERE referenced_object_id = @objid
        ORDER BY 1
    ELSE
        RAISERROR (
                15470
                ,- 1
                ,- 1
                ,@objname
                ) — No foreign keys reference table ‘%ls’. 

    /*********************************CONSTRAINT FETCH********************************/

    IF OBJECT_ID(‘tempdb..#spcnsttab’) IS NOT NULL
    BEGIN
        DROP TABLE #spcnsttab
    END

    DECLARE @cnstdes NVARCHAR(4000) — string to build up index desc 
        ,@cnstname SYSNAME — name of const. currently under consideration 
        ,@i INT
        ,@cnstid INT
        ,@cnsttype CHAR(2)
        ,@keys NVARCHAR(2126) –Length (16*max_identifierLength)+(15*2)+(16*3) 

    — Create temp table 
    CREATE TABLE #spcnsttab (
        cnst_id INT NOT NULL
        ,cnst_type NVARCHAR(146) collate database_default NOT NULL — 128 for name + text for DEFAULT 
        ,cnst_name SYSNAME collate database_default NOT NULL
        ,cnst_nonblank_name SYSNAME collate database_default NOT NULL
        ,cnst_2type CHAR(2) collate database_default NULL
        ,cnst_disabled BIT NULL
        ,cnst_notrepl BIT NULL
        ,cnst_delcasc BIT NULL
        ,cnst_updcasc BIT NULL
        ,cnst_keys NVARCHAR(2126) collate database_default NULL — see @keys above for length descr 
        )

    — Check to see that the object names are local to the current database. 
    SELECT @dbname = parsename(@objname, 3)

    IF @dbname IS NULL
        SELECT @dbname = db_name()
    ELSE
        IF @dbname <> db_name()
        BEGIN
            RAISERROR (
                    15250
                    ,- 1
                    ,- 1
                    )
                –return (1) 
        END

    — Check to see if the table exists and initialize @objid. 
    SELECT @objid = object_id(@objname)

    IF @objid IS NULL
    BEGIN
        RAISERROR (
                15009
                ,- 1
                ,- 1
                ,@objname
                ,@dbname
                )
            –return (1) 
    END

    — STATIC CURSOR OVER THE TABLE’S CONSTRAINTS 
    DECLARE ms_crs_cnst CURSOR LOCAL STATIC
    FOR
    SELECT object_id
        ,type
        ,NAME
    FROM sys.objects
    WHERE parent_object_id = @objid
        AND type IN (
            ‘C ‘
            ,’PK’
            ,’UQ’
            ,’F ‘
            ,’D ‘
            ) — ONLY 6.5 sysconstraints objects 
    FOR READ ONLY

    — Now check out each constraint, figure out its type and keys and 
    — save the info in a temporary table that we’ll print out at the end. 
    OPEN ms_crs_cnst

    FETCH ms_crs_cnst
    INTO @cnstid
        ,@cnsttype
        ,@cnstname

    WHILE @@fetch_status >= 0
    BEGIN
        IF @cnsttype IN (
                ‘PK’
                ,’UQ’
                )
        BEGIN
            — get indid and index description 
            DECLARE @indid SMALLINT

            SELECT @indid = index_id
                ,@cnstdes = CASE
                    WHEN @cnsttype = ‘PK’
                        THEN ‘PRIMARY KEY’
                    ELSE ‘UNIQUE’
                    END + CASE
                    WHEN index_id = 1
                        THEN ‘ (clustered)’
                    ELSE ‘ (non-clustered)’
                    END
            FROM sys.indexes
            WHERE object_id = @objid
                AND NAME = object_name(@cnstid)

            — Format keys string 
            DECLARE @thiskey NVARCHAR(131) — 128+3 

            SELECT @keys = index_col(@objname, @indid, 1)
                ,@i = 2

            IF (indexkey_property(@objid, @indid, 1, ‘isdescending’) = 1)
                SELECT @keys = @keys + ‘(-)’

            SELECT @thiskey = index_col(@objname, @indid, @i)

            IF (
                    (@thiskey IS NOT NULL)
                    AND (indexkey_property(@objid, @indid, @i, ‘isdescending’) = 1)
                    )
                SELECT @thiskey = @thiskey + ‘(-)’

            WHILE (@thiskey IS NOT NULL)
            BEGIN
                SELECT @keys = @keys + ‘, ‘ + @thiskey
                    ,@i = @i + 1

                SELECT @thiskey = index_col(@objname, @indid, @i)

                IF (
                        (@thiskey IS NOT NULL)
                        AND (indexkey_property(@objid, @indid, @i, ‘isdescending’) = 1)
                        )
                    SELECT @thiskey = @thiskey + ‘(-)’
            END

            — ADD TO TABLE 
            INSERT INTO #spcnsttab (
                cnst_id
                ,cnst_type
                ,cnst_name
                ,cnst_nonblank_name
                ,cnst_keys
                ,cnst_2type
                )
            VALUES (
                @cnstid
                ,@cnstdes
                ,@cnstname
                ,@cnstname
                ,@keys
                ,@cnsttype
                )
        END
        ELSE
            IF @cnsttype = ‘F ‘
            BEGIN
                — OBTAIN TWO TABLE IDs 
                DECLARE @fkeyid INT
                    ,@rkeyid INT

                SELECT @fkeyid = parent_object_id
                    ,@rkeyid = referenced_object_id
                FROM sys.foreign_keys
                WHERE object_id = @cnstid

                — USE CURSOR OVER FOREIGN KEY COLUMNS TO BUILD COLUMN LISTS 
                — (NOTE: @keys HAS THE FKEY AND @cnstdes HAS THE RKEY COLUMN LIST) 
                DECLARE ms_crs_fkey CURSOR LOCAL
                FOR
                SELECT parent_column_id
                    ,referenced_column_id
                FROM sys.foreign_key_columns
                WHERE constraint_object_id = @cnstid

                OPEN ms_crs_fkey

                DECLARE @fkeycol SMALLINT
                    ,@rkeycol SMALLINT

                FETCH ms_crs_fkey
                INTO @fkeycol
                    ,@rkeycol

                SELECT @keys = col_name(@fkeyid, @fkeycol)
                    ,@cnstdes = col_name(@rkeyid, @rkeycol)

                FETCH ms_crs_fkey
                INTO @fkeycol
                    ,@rkeycol

                WHILE @@fetch_status >= 0
                BEGIN
                    SELECT @keys = @keys + ‘, ‘ + col_name(@fkeyid, @fkeycol)
                        ,@cnstdes = @cnstdes + ‘, ‘ + col_name(@rkeyid, @rkeycol)

                    FETCH ms_crs_fkey
                    INTO @fkeycol
                        ,@rkeycol
                END

                DEALLOCATE ms_crs_fkey

                — ADD ROWS FOR BOTH SIDES OF FOREIGN KEY 
                INSERT INTO #spcnsttab (
                    cnst_id
                    ,cnst_type
                    ,cnst_name
                    ,cnst_nonblank_name
                    ,cnst_keys
                    ,cnst_disabled
                    ,cnst_notrepl
                    ,cnst_delcasc
                    ,cnst_updcasc
                    ,cnst_2type
                    )
                VALUES (
                    @cnstid
                    ,’FOREIGN KEY’
                    ,@cnstname
                    ,@cnstname
                    ,@keys
                    ,ObjectProperty(@cnstid, ‘CnstIsDisabled’)
                    ,ObjectProperty(@cnstid, ‘CnstIsNotRepl’)
                    ,ObjectProperty(@cnstid, ‘CnstIsDeleteCascade’)
                    ,ObjectProperty(@cnstid, ‘CnstIsUpdateCascade’)
                    ,@cnsttype
                    )

                INSERT INTO #spcnsttab (
                    cnst_id
                    ,cnst_type
                    ,cnst_name
                    ,cnst_nonblank_name
                    ,cnst_keys
                    ,cnst_2type
                    )
                SELECT @cnstid
                    ,’ ‘
                    ,’ ‘
                    ,@cnstname
                    ,’REFERENCES ‘ + db_name() + ‘.’ + rtrim(schema_name(ObjectProperty(@rkeyid, ‘schemaid’))) + ‘.’ + object_name(@rkeyid) + ‘ (‘ + @cnstdes + ‘)’
                    ,@cnsttype
            END
            ELSE
                IF @cnsttype = ‘C’
                BEGIN
                    — Check constraint 
                    SELECT @i = 1

                    SELECT @cnstdes = NULL

                    SELECT @cnstdes = TEXT
                    FROM syscomments
                    WHERE id = @cnstid
                        AND colid = @i

                    INSERT INTO #spcnsttab (
                        cnst_id
                        ,cnst_type
                        ,cnst_name
                        ,cnst_nonblank_name
                        ,cnst_keys
                        ,cnst_disabled
                        ,cnst_notrepl
                        ,cnst_2type
                        )
                    SELECT @cnstid
                        ,CASE
                            WHEN parent_column_id <> 0
                                THEN ‘CHECK on column ‘ + col_name(@objid, parent_column_id)
                            ELSE ‘CHECK Table Level ‘
                            END
                        ,@cnstname
                        ,@cnstname
                        ,substring(@cnstdes, 1, 2000)
                        ,is_disabled
                        ,is_not_for_replication
                        ,@cnsttype
                    FROM sys.check_constraints
                    WHERE object_id = @cnstid

                    WHILE @cnstdes IS NOT NULL
                    BEGIN
                        IF @i > 1
                            INSERT INTO #spcnsttab (
                                cnst_id
                                ,cnst_type
                                ,cnst_name
                                ,cnst_nonblank_name
                                ,cnst_keys
                                ,cnst_2type
                                )
                            SELECT @cnstid
                                ,’ ‘
                                ,’ ‘
                                ,@cnstname
                                ,substring(@cnstdes, 1, 2000)
                                ,@cnsttype

                        IF len(@cnstdes) > 2000
                            INSERT INTO #spcnsttab (
                                cnst_id
                                ,cnst_type
                                ,cnst_name
                                ,cnst_nonblank_name
                                ,cnst_keys
                                ,cnst_2type
                                )
                            SELECT @cnstid
                                ,’ ‘
                                ,’ ‘
                                ,@cnstname
                                ,substring(@cnstdes, 2001, 2000)
                                ,@cnsttype

                        SELECT @i = @i + 1

                        SELECT @cnstdes = NULL

                        SELECT @cnstdes = TEXT
                        FROM syscomments
                        WHERE id = @cnstid
                            AND colid = @i
                    END
                END
                ELSE
                    IF (@cnsttype = ‘D’)
                    BEGIN
                        SELECT @i = 1

                        SELECT @cnstdes = NULL

                        SELECT @cnstdes = TEXT
                        FROM syscomments
                        WHERE id = @cnstid
                            AND colid = @i

                        INSERT INTO #spcnsttab (
                            cnst_id
                            ,cnst_type
                            ,cnst_name
                            ,cnst_nonblank_name
                            ,cnst_keys
                            ,cnst_2type
                            )
                        SELECT @cnstid
                            ,’DEFAULT on column ‘ + col_name(@objid, parent_column_id)
                            ,@cnstname
                            ,@cnstname
                            ,substring(@cnstdes, 1, 2000)
                            ,@cnsttype
                        FROM sys.default_constraints
                        WHERE object_id = @cnstid

                        WHILE @cnstdes IS NOT NULL
                        BEGIN
                            IF @i > 1
                                INSERT INTO #spcnsttab (
                                    cnst_id
                                    ,cnst_type
                                    ,cnst_name
                                    ,cnst_nonblank_name
                                    ,cnst_keys
                                    ,cnst_2type
                                    )
                                SELECT @cnstid
                                    ,’ ‘
                                    ,’ ‘
                                    ,@cnstname
                                    ,substring(@cnstdes, 1, 2000)
                                    ,@cnsttype

                            IF len(@cnstdes) > 2000
                                INSERT INTO #spcnsttab (
                                    cnst_id
                                    ,cnst_type
                                    ,cnst_name
                                    ,cnst_nonblank_name
                                    ,cnst_keys
                                    ,cnst_2type
                                    )
                                SELECT @cnstid
                                    ,’ ‘
                                    ,’ ‘
                                    ,@cnstname
                                    ,substring(@cnstdes, 2001, 2000)
                                    ,@cnsttype

                            SELECT @i = @i + 1

                            SELECT @cnstdes = NULL

                            SELECT @cnstdes = TEXT
                            FROM syscomments
                            WHERE id = @cnstid
                                AND colid = @i
                        END
                    END

        FETCH ms_crs_cnst
        INTO @cnstid
            ,@cnsttype
            ,@cnstname
    END –of major loop 

    DEALLOCATE ms_crs_cnst

    — Find any rules or defaults bound by the sp_bind… method. 
    INSERT INTO #spcnsttab (
        cnst_id
        ,cnst_type
        ,cnst_name
        ,cnst_nonblank_name
        ,cnst_keys
        ,cnst_2type
        )
    SELECT c.rule_object_id
        ,’RULE on column ‘ + c.NAME + ‘ (bound with sp_bindrule)’
        ,object_name(c.rule_object_id)
        ,object_name(c.rule_object_id)
        ,m.TEXT
        ,’R ‘
    FROM sys.columns c
    INNER JOIN syscomments m ON m.id = c.rule_object_id
    WHERE c.object_id = @objid

    INSERT INTO #spcnsttab (
        cnst_id
        ,cnst_type
        ,cnst_name
        ,cnst_nonblank_name
        ,cnst_keys
        ,cnst_2type
        )
    SELECT c.default_object_id
        ,’DEFAULT on column ‘ + c.NAME + ‘ (bound with sp_bindefault)’
        ,object_name(c.default_object_id)
        ,object_name(c.default_object_id)
        ,m.TEXT
        ,’D ‘
    FROM sys.columns c
    INNER JOIN syscomments m ON m.id = c.default_object_id
    WHERE c.object_id = @objid
        AND objectproperty(c.default_object_id, ‘IsConstraint’) = 0

    — Now print out the contents of the temporary index table. 
    IF EXISTS (
            SELECT *
            FROM #spcnsttab
            )
    BEGIN
        DELETE
        FROM info.[Constraint]
        WHERE TableName = @objname

        INSERT INTO info.[Constraint]
        SELECT @objname
            ,’constraint_type’ = cnst_type
            ,’constraint_name’ = cnst_name
            ,’delete_action’ = CASE
                WHEN cnst_name = ‘ ‘
                    THEN ‘ ‘
                WHEN cnst_2type IN (‘F ‘)
                    THEN CASE
                            WHEN cnst_delcasc = 1
                                THEN ‘Cascade’
                            ELSE ‘No Action’
                            END
                ELSE ‘(n/a)’
                END
            ,’update_action’ = CASE
                WHEN cnst_name = ‘ ‘
                    THEN ‘ ‘
                WHEN cnst_2type IN (‘F ‘)
                    THEN CASE
                            WHEN cnst_updcasc = 1
                                THEN ‘Cascade’
                            ELSE ‘No Action’
                            END
                ELSE ‘(n/a)’
                END
            ,’status_enabled’ = CASE
                WHEN cnst_name = ‘ ‘
                    THEN ‘ ‘
                WHEN cnst_2type IN (
                        ‘F ‘
                        ,’C ‘
                        )
                    THEN CASE
                            WHEN cnst_disabled = 1
                                THEN ‘Disabled’
                            ELSE ‘Enabled’
                            END
                ELSE ‘(n/a)’
                END
            ,’status_for_replication’ = CASE
                WHEN cnst_name = ‘ ‘
                    THEN ‘ ‘
                WHEN cnst_2type IN (
                        ‘F ‘
                        ,’C ‘
                        )
                    THEN CASE
                            WHEN cnst_notrepl = 1
                                THEN ‘Not_For_Replication’
                            ELSE ‘Is_For_Replication’
                            END
                ELSE ‘(n/a)’
                END
            ,’constraint_keys’ = cnst_keys
        FROM #spcnsttab
        ORDER BY cnst_nonblank_name
            ,cnst_name DESC
    END
    ELSE
        RAISERROR (
                15469
                ,- 1
                ,- 1
                ,@objname
                )

    /*******************************************************************************/
    IF (
            SELECT count(*)
            FROM sysdepends
            WHERE depid = @objid
                AND deptype = 1
            ) = 0
    BEGIN
        RAISERROR (
                15647
                ,- 1
                ,- 1
                ,@objname
                ) — No views with schemabinding reference table ‘%ls’. 
    END
    ELSE
    BEGIN
        SELECT DISTINCT ‘Table is referenced by views’ = obj.NAME
        FROM sys.objects obj
            ,sysdepends deps
        WHERE obj.type = ‘V’
            AND obj.object_id = deps.id
            AND deps.depid = @objid
            AND deps.deptype = 1
        GROUP BY obj.NAME
    END
END
ELSE
    IF @sysobj_type IN (‘V ‘)
        AND @objid > 0
    BEGIN
        — VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID 
        PRINT ‘ ‘

        RAISERROR (
                15469
                ,- 1
                ,- 1
                ,@objname
                ) — No constraints defined 

        PRINT ‘ ‘

        RAISERROR (
                15470
                ,- 1
                ,- 1
                ,@objname
                ) — No foreign keys reference table ‘%ls’. 

        EXEC sys.sp_helpindex @objname
    END
    SET @loopCnt = @loopCnt + 1
    END
END

After execute this you have the procedure in your database.

now need to execute the procedure:
EXEC Info_Table

And Select the tables which we created above

SELECT *
FROM info.Columns

SELECT *
FROM info.[Constraint]

SELECT *
FROM info.FileGroup

SELECT *
FROM info.Identity_Col

SELECT *
FROM info.[Index]

SELECT *
FROM info.OWNER

SELECT *
FROM info.RowGUID

SELECT *
FROM info.Table_RefrenceBy_col

you have all table information in these tables, as name shows of every table there is no need to explain, which table is used for which information.

just enjoy with these few tables with bunch of all info in your hand.

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |

Scope and lifetime of temporary tables

Hi Folks,

Its bit easy to understand the scope and lifetime of Global temporary table (##), but Local temporary tables (#) create many misconception about its scope and lifetime.

Temporary tables (#) are only available at the current session (current window). But in current session there is also too much misconception.

So what is the exact scope or calling area for a temporary table?

For such type of temporary tables (#) SQL follow the inheritance concept, Like:

Inner function can have all the objects of outer function but outer functions not have permission to use the objects of inner function.

in short way child can use all objects of parents but parents cant do the same for child’s objects.

Means, child can access a Temporary tables (#) created in the parent,

Example :

exec(‘select  * into #temp from MyTable; select * from #temp’)

select * from #temp

The #temp table gets created and executed once as seen in results panel.

Then got an error message in messages panel shows: Invalid object name ‘#temp’.

We see that we created the #temp table in the child session,

so as inheritance rule we can’t cal that temporary table it in the parent session, because a temporary table gets

dropped when it goes out of session/scope which happens after exec() finishes it’s execution

Example:

‘select  * into #temp from MyTable

exec(‘select * from #temp’)

select * from #temp

The #temp table gets created and queried twice as seen in results panel.

Here the #temp table is created in the parent session so the child can access it freely.

The temp table gets dropped when the parent session end or can drop table manually.

Same for the procedure :

Example

create table tabe ([ename] varchar(max))

GO

Create procedure Proc1

as

begin

select top 1 [ename] into #temp from [Emp]

exec NestdProc

End

GO

Create procedure NestdProc

as

begin

insert into tabe

select top 1 t.[ename] from [Emp] B

join #temp t on B.[ename] =t.[ename]

END

And Finally you have a record in newly created table

SELECT * FROM tabe

Hope you will like this post on Microsoft SQL Server.

If you really like reading my blog and understood at least few thing then

please don’t forget to subscribe my blog.

Connect With me on

| linkedIn | Google+ | WordPress |