Without wizard you can import/export data/schema in sql server

Without wizard you can import/export data/schema..
——————————————————————

CREATE PROCEDURE [dbo].[sp_Table_Data_Script]
@SchemaName VARCHAR(MAX),
@TableName VARCHAR(Max),
@WhereClause NVARCHAR(Max),
@TopNo INT
AS

DECLARE @SQL VARCHAR(max)
DECLARE @nSQL NVARCHAR(Max)
DECLARE @RecordCount INT
DECLARE @FirstColumn NVARCHAR(Max)

–SET @TableName=QUOTENAME(@TableName) — Quote the table name
SET @TableName =@SchemaName + ‘.’ + QUOTENAME(@TableName)
Print @TableName

/*We need to find the record count in order to remove Union ALL from the last row*/
SET @nSQL=”
SET @nSQL= N’SELECT @RecordCount=COUNT(*) FROM ‘ + @TableName + (CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ Where ‘ + @Whereclause ELSE ” END)
Print @nSQL
EXEC sp_executesql @query = @nSQL, @params = N’@RecordCount INT OUTPUT’, @RecordCount = @RecordCount OUTPUT
/*****************************************************************/

/*Need to check either top No of record is less than record count
in order to remove union all from the last row */
IF @TopNo IS NOT NULL And @TopNo<@RecordCount
BEGIN
SET @RecordCount=@TopNo
END
/***************************************************************/
/*** Here we need to find the first column name to generate a serial number and insert an "Insert into statement" in the first row ***/
SET @nSQL=''
SET @nSQL= N'SELECT @FirstColumn=[name] FROM sys.columns WHERE [Column_id]=1 And object_ID=object_ID('''+ @TableName + ''')'
Print @nSQL
EXEC sp_executesql @query = @nSQL, @params = N'@FirstColumn nvarchar(Max) OUTPUT', @FirstColumn = @FirstColumn OUTPUT
/**************************************************************/

DECLARE @FieldName VARCHAR(max)
SET @FieldName=''

If (Select Count(*) FROM sys.columns WHERE object_id=object_id('' + @TableName + '') And is_identity0)=1

BEGIN
SET @FieldName = STUFF(
(
SELECT ‘,’ + QUOTENAME([Name]) FROM sys.columns WHERE object_id=object_id(” + @TableName + ”) Order By [column_id]
FOR XML PATH(”)), 1, 1, ”)
Set @FieldName =’(‘ + @FieldName + ‘)’
Print @FieldName
Print len(@FieldName)
END

/*******Create list of comma seperated columns *******/
SET @SQL= (SELECT STUFF((SELECT(CASE
WHEN system_type_id In (167,175,189) THEN + ‘ Cast(ISNULL(LTRIM(RTRIM(”N”””+Replace(‘ + QUOTENAME([Name])+ ‘,””””,””””””)+””””’+ ‘)),”NULL”) as varchar(max)) + ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
WHEN system_type_id In (231,239) THEN + ‘ Cast(ISNULL(LTRIM(RTRIM(”N”””+Replace(‘ + QUOTENAME([Name])+ ‘,””””,””””””)+””””’+ ‘)),”NULL”) as nvarchar(max)) + ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
WHEN system_type_id In (58,61,36) THEN + ‘ ISNULL(LTRIM(RTRIM(”N””” + Cast(‘ + QUOTENAME([Name])+ ‘ as varchar(max))+””””’ + ‘)),”NULL”) + ” AS ‘ + QUOTENAME([Name]) + ”’+ ” ,”’+’ + ‘
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN + ‘ ISNULL(Cast(‘ + QUOTENAME([Name])+ ‘ as varchar(max)),”NULL”)+ ” AS ‘ + QUOTENAME([Name]) + ”’ + ” ,”’+’+ ‘
END
)
FROM
sys.columns WHERE object_ID=object_ID(”+ @TableName + ”) FOR XML PATH(”)),1,1,’ ‘))

/*******************************************************/

/* Here 500 means if the record count is 500 or top no 500 then it will generate “Insert into select ..Union All ”
Because more than 500 might reduce its performance. */
IF @TopNo <500 or @RecordCount<500
BEGIN
IF @TopNo IS NULL
BEGIN
SET @SQl='SELECT (Case When ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') =1 THEN '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' ''' + ' ELSE '''' END) + ''SELECT ''+'
+ Left(@SQL,Len(@SQL)-8) + ' + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') ‘ + CONVERT(VARCHAR(10),@RecordCount)
+ ‘ THEN ” UNION ALL” ELSE ”” END) AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
END
ELSE
BEGIN
SET @SQl= ‘SELECT TOP ‘ + CONVERT(VARCHAR(10),@TopNo) + ‘ (CASE WHEN ROW_NUMBER() OVER (ORDER BY ‘ + QUOTENAME(@FirstColumn) + ‘) =1 THEN ” INSERT INTO ‘
+ @TableName + ‘ ‘ + ‘ ‘+ @FieldName + ”” + ‘ ELSE ”” END) + ”SELECT ”+’ + LEFT(@SQL,LEN(@SQL)-8) + ‘ + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ‘ + QUOTENAME(@FirstColumn) + ‘) ‘
+ CONVERT(VARCHAR(10),@RecordCount) + ‘ THEN ” Union All” ELSE ”” END) AS [Name]‘ + ‘ FROM ‘ + @TableName + (CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
END
END
ELSE
— Greator then 500 will generate “insert into select *” … for each record.
BEGIN
IF @TopNo IS NULL
BEGIN
SET @SQl=’SELECT ” INSERT INTO ‘ + @TableName + ‘ ‘ + ‘ ‘+ @FieldName + ‘ ” + ”SELECT ”+’ + Left(@SQL,Len(@SQL)-8)
+ ‘ AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
END
ELSE
BEGIN
SET @SQl=’SELECT TOP ‘ + CONVERT(VARCHAR(10),@TopNo) + ”’ INSERT INTO ‘ + @TableName + ‘ ‘ + ‘ ‘+ @FieldName
+ ‘ ” + ”SELECT ”+’ + Left(@SQL,Len(@SQL)-8) + ‘ AS [Name] ‘ + ‘ FROM ‘ + @TableName +(CASE WHEN ISNULL(@Whereclause,”) ” THEN ‘ WHERE ‘ + @Whereclause ELSE ” END)
END
END
EXEC (@SQL)

GO

CREATE PROCEDURE [dbo].[spS_DatabaseInformationGet]
@getValue VARCHAR(50) = NULL
AS

SET NOCOUNT ON;

IF @getValue = ‘DATABASE’
BEGIN
SELECT [name]
FROM sys.databases
END
ELSE IF @getValue = ‘TABLE’
BEGIN
declare @TableList TABLE
(
name nvarchar(max)
)
DECLARE @name VARCHAR(128),@setvalgo varchar(5)
SET @setvalgo = ‘GO’
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘U’ AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
insert into @TableList
SELECT ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +RTRIM(@name)+”’) AND type in (N”U”)) DROP TABLE ‘+RTRIM(@name)
–SELECT ‘DROP TABLE [dbo].[' + RTRIM(@name) +']‘
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘U’ AND category = 0 AND [name] > @name ORDER BY [name])
insert into @TableList
select @setvalgo
END

insert into @TableList
SELECT ‘create table [' + so.name + '] (‘ + o.list + ‘)’ + CASE WHEN tc.Constraint_Name IS NULL THEN ” ELSE ‘ALTER TABLE ‘ + so.Name + ‘ ADD CONSTRAINT ‘ + tc.Constraint_Name + ‘ PRIMARY KEY ‘ + ‘ (‘ + LEFT(j.List, Len(j.List)-1) + ‘)’ END
as name
from sysobjects so
cross apply
(SELECT
‘ ['+column_name+'] ‘ +
data_type + case data_type
when ‘sql_variant’ then ”
when ‘text’ then ”
when ‘decimal’ then ‘(‘ + cast(numeric_precision_radix as varchar) + ‘, ‘ + cast(numeric_scale as varchar) + ‘)’
else coalesce(‘(‘+case when character_maximum_length = -1 then ‘MAX’ else cast(character_maximum_length as varchar) end +’)’,”) end + ‘ ‘ +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,’IsIdentity’) = 1
) then
‘IDENTITY(‘ +
cast(ident_seed(so.name) as varchar) + ‘,’ +
cast(ident_incr(so.name) as varchar) + ‘)’
else ”
end + ‘ ‘ +
(case when IS_NULLABLE = ‘No’ then ‘NOT ‘ else ” end ) + ‘NULL ‘ +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN ‘DEFAULT ‘+ information_schema.columns.COLUMN_DEFAULT ELSE ” END + ‘, ‘
from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH(”)) o (list)
left join
information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = ‘PRIMARY KEY’
cross apply
(select ‘[' + Column_Name + '], ‘
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH(”)) j (list)
where xtype = ‘U’
AND name NOT IN (‘dtproperties’)
select * from @TableList
END
ELSE IF @getValue = ‘TABLE_DATA’
BEGIN
DECLARE @TableName VARCHAR(1000)
DECLARE @TableDataRow BIGINT
DECLARE @TabIdenOn varchar(100)
DECLARE @TabIdenOff varchar(100)
create table #temp(name varchar(MAX))
–Cursor
DECLARE @getTable CURSOR
SET @getTable = CURSOR FOR
SELECT TABLE_NAME
FROM information_Schema.tables
OPEN @getTable
FETCH NEXT
FROM @getTable INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TableDataRow=rows FROM sys.partitions
WHERE object_id = object_id(@TableName)
AND index_id 0
BEGIN
IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N”+@TableName+”), ‘TableHasIdentity’)) = 1)
BEGIN
set @TabIdenOn = ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ ON’
set @TabIdenOff = ‘SET IDENTITY_INSERT ‘ + @TableName + ‘ OFF’
insert into #temp(name)
select @TabIdenOn
insert into #temp(name)
exec sp_Table_Data_Script ”,@TableName,NULL,NULL
insert into #temp(name)
select @TabIdenOff
END
ELSE
BEGIN
insert into #temp(name)
exec sp_Table_Data_Script ”,@TableName,NULL,NULL
END

END
FETCH NEXT
FROM @getTable INTO @TableName
END
CLOSE @getTable
DEALLOCATE @getTable

SELECT name
FROM #temp
DROP TABLE #temp
END
ELSE IF @getValue = ‘P_F_T_V_SCRIPT’
BEGIN
–SELECT object_definition(object_id) AS name
–FROM sys.objects
–WHERE type_desc in (‘SQL_SCALAR_FUNCTION’,
– ‘SQL_STORED_PROCEDURE’,
– ‘SQL_TABLE_VALUED_FUNCTION’,
– ‘SQL_TRIGGER’,
– ‘VIEW’)

DECLARE @ProcName VARCHAR(200),@FunName VARCHAR(200),@PExist VARCHAR(MAX)
DECLARE @MyCursor CURSOR,@MyCursorSPS CURSOR,@MyCursorFUN CURSOR,@MyCursorFUNS CURSOR
DECLARE @setval1 VARCHAR(25),@setval2 VARCHAR(5),@setval3 VARCHAR(25),@setval4 VARCHAR(5)
SET @setval1=’SET ANSI_NULLS ON ‘
SET @setval2=’GO’
SET @setval3=’SET QUOTED_IDENTIFIER ON’

DECLARE @StoredProcsList TABLE
(
name TEXT
)
–Add Go
INSERT INTO @StoredProcsList
SELECT @setval2

DECLARE @SPCHECKEXIST CURSOR
SET @SPCHECKEXIST = CURSOR FOR
–Select
SELECT name FROM sys.objects WHERE type = ‘P’
OPEN @SPCHECKEXIST
FETCH NEXT
FROM @SPCHECKEXIST INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PExist= ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +@ProcName+”’) AND type in (N”P”, N”PC”)) DROP PROCEDURE ‘+@ProcName
INSERT INTO @StoredProcsList
SELECT @PExist
FETCH NEXT
FROM @SPCHECKEXIST INTO @ProcName
END
CLOSE @SPCHECKEXIST
DEALLOCATE @SPCHECKEXIST

DECLARE @FUNCHECKEXIST CURSOR
SET @FUNCHECKEXIST = CURSOR FOR
–Select
SELECT name FROM sys.objects where type_desc in (‘SQL_SCALAR_FUNCTION’,’SQL_TABLE_VALUED_FUNCTION’)
OPEN @FUNCHECKEXIST
FETCH NEXT
FROM @FUNCHECKEXIST INTO @FunName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PExist= ‘IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N”’ +@FunName+”’) AND type in (N”FN”, N”IF”, N”TF”, N”FS”, N”FT”)) DROP FUNCTION ‘+@FunName
INSERT INTO @StoredProcsList
SELECT @PExist
FETCH NEXT
FROM @FUNCHECKEXIST INTO @FunName
END
CLOSE @FUNCHECKEXIST
DEALLOCATE @FUNCHECKEXIST

DECLARE @SPGENERATESCRIPT CURSOR
SET @SPGENERATESCRIPT = CURSOR FOR
–Select
SELECT name FROM sys.objects WHERE type = ‘P’
OPEN @SPGENERATESCRIPT
FETCH NEXT
FROM @SPGENERATESCRIPT INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @StoredProcsList
SELECT @setval1
INSERT INTO @StoredProcsList
SELECT @setval2
INSERT INTO @StoredProcsList
SELECT @setval3
INSERT INTO @StoredProcsList
SELECT @setval2
INSERT INTO @StoredProcsList
EXEC sp_helptext @ProcName
FETCH NEXT
FROM @SPGENERATESCRIPT INTO @ProcName
END
CLOSE @SPGENERATESCRIPT
DEALLOCATE @SPGENERATESCRIPT

DECLARE @FUNGENERATESCRIPT CURSOR
SET @FUNGENERATESCRIPT = CURSOR FOR
–Select
SELECT name FROM sys.objects where type_desc in (‘SQL_SCALAR_FUNCTION’,’SQL_TABLE_VALUED_FUNCTION’)
OPEN @FUNGENERATESCRIPT
FETCH NEXT
FROM @FUNGENERATESCRIPT INTO @FunName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @StoredProcsList
SELECT @setval1
INSERT INTO @StoredProcsList
SELECT @setval2
INSERT INTO @StoredProcsList
SELECT @setval3
INSERT INTO @StoredProcsList
SELECT @setval2
INSERT INTO @StoredProcsList
EXEC sp_helptext @FunName
INSERT INTO @StoredProcsList
SELECT @setval2
FETCH NEXT
FROM @FUNGENERATESCRIPT INTO @FunName
END
CLOSE @FUNGENERATESCRIPT
DEALLOCATE @FUNGENERATESCRIPT

SELECT name FROM @StoredProcsList

END
ELSE IF @getValue = ‘D_USER’
BEGIN
select princ.name
, princ.type_desc
, perm.permission_name
, perm.state_desc
, perm.class_desc
, object_name(perm.major_id)
from sys.database_principals princ
left join
sys.database_permissions perm
on perm.grantee_principal_id = princ.principal_id
where type_desc in (‘SQL_USER’) and state_desc in (‘GRANT’)
END

GO