USE [database_name]
GO
SELECT * FROM sys.tables
--------------------------------------------------------------------------
USE master
GO
SELECT * FROM sys.tables
SELECT * FROM sys.types
SELECT * FROM sys.columns
------------------INNER JOIN on system_type_id-----------------------------------
SELECT * FROM SYS.COLUMNS sc INNER JOIN sys.types st ON sc.system_type_id=st.system_type_id where object_id=object_id('emp')
--concatenation column_name,data_type,size
declare @concatname varchar(1000)=''
select @concatname=@concatname+',@'+SC.name+' '+ST.name+'('+cast(st.max_length as varchar(50))+')' from sys.columns SC INNER JOIN sys.types ST ON SC.system_type_id=ST.system_type_id WHERE OBJECT_ID=OBJECT_ID('EMP')
print RIGHT(@concatname,LEN(@concatname)-1)
----------------------CREATE PROCEDURE IMPLICITILY-------------------------------------------
CREATE PROC val_insert(@tblname VARCHAR(100),@tbl_insert VARCHAR(100))
AS
BEGIN
DECLARE @parameter NVARCHAR(1000)=''
SELECT @parameter=@parameter+','+s.name+''
FROM sys.columns s inner join sys.types t on s.system_type_id=t.system_type_id WHERE OBJECT_ID=OBJECT_ID('EMP')
--print RIGHT(@parameter,LEN(@parameter))
DECLARE @concat NVARCHAR(1000)=''
SELECT @concat=@concat+'@'+s.name+' '+t.name+''+'('+cast(t.max_length AS VARCHAR(10))+')'+','
FROM sys.columns s inner join sys.types t on s.system_type_id=t.system_type_id WHERE OBJECT_ID=OBJECT_ID('EMP')
PRINT 'CREATE PROC '+@tblname+'('print LEFT(RIGHT(@concat,LEN(@concat)),LEN(@concat)-1)+')'
PRINT 'AS'
PRINT 'BEGIN'
PRINT +' '+'insert into'+' '+@tbl_insert+' '+'values('print RIGHT(@parameter,LEN(@parameter)-1) +')'
PRINT 'END'
PRINT 'EXEC'+' '+@tblname
END
EXEC val_insert 'EMP','TBL'
