Tyler
asked on
Clone table from one server.database to another server.database
clonetable.sql
I am wanting to copy a table, its structure, indexes primary keys constraints etc. how can this be done via t-script or Stored procedure.
I keep getting this error.
Msg 117, Level 15, State 1, Line 16
The object name 'SERVERNAME.DATABASENAME' contains more than the maximum number of prefixes. The maximum is 2.
I'm using a linked servers and SQL server 2016. any suggestions?
I am wanting to copy a table, its structure, indexes primary keys constraints etc. how can this be done via t-script or Stored procedure.
I keep getting this error.
Msg 117, Level 15, State 1, Line 16
The object name 'SERVERNAME.DATABASENAME' contains more than the maximum number of prefixes. The maximum is 2.
I'm using a linked servers and SQL server 2016. any suggestions?
ASKER
USE [TyLer]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[spCloneTable]
@SourceSchema = N'DBO',
@SourceTable = N'AE420',
@DestinationSchema = N'tyler2].[DW].[DBO',
@DestinationTable = N'AE420',
@RecreateIfExists = 1
SELECT 'Return Value' = @return_value
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[spCloneTable]
@SourceSchema = N'DBO',
@SourceTable = N'AE420',
@DestinationSchema = N'tyler2].[DW].[DBO',
@DestinationTable = N'AE420',
@RecreateIfExists = 1
SELECT 'Return Value' = @return_value
This is your problem:
@DestinationSchema = N'tyler2].[DW].[DBO',
Try:
@DestinationSchema = N'DBO',
@DestinationSchema = N'tyler2].[DW].[DBO',
Try:
@DestinationSchema = N'DBO',
ASKER
understand but i want it to write to another server thus what i put in 'tyler2].[DW].[DBO' will include the linked server name in the schema coulmn creating '[Tyler2].[DW].[DBO].[ae42 0] reference in the dynamic script when executed. you need to read the script and understand what the error is saying.
The error
The object name 'SERVERNAME.DATABASENAME' contains more than the maximum number of prefixes. The maximum is 2.
here is what the sp generated
SELECT TOP (0) * INTO [tyler2].[dw].[DBO].[AE420 ] FROM [DBO].[AE420] and it returns the above error
The error
The object name 'SERVERNAME.DATABASENAME' contains more than the maximum number of prefixes. The maximum is 2.
here is what the sp generated
SELECT TOP (0) * INTO [tyler2].[dw].[DBO].[AE420
Do you have defined the linked server with the name tyler2? If yes how do you refer to a table from a database on that server? I mean a simple select that works.
ASKER
OK so what i found is to create a table on a remote server you need to write the query like this:
If anyone has a better solution please share.
exec ('USE adventureworks;
CREATE TABLE [dbo].[ae420]
(
[Id] [INT] NOT NULL IDENTITY(1,1)
, [grbreknr] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [factnr] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [cdherkomst] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [cdorder] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [prijs] [DECIMAL](10,2) NULL
, [regelbedr] [DECIMAL](10,2) NULL
, [cdprodukt] [VARCHAR](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
, [aantgelev] [INT] NULL
, CONSTRAINT [PK__ae420__3214EC071690F272] PRIMARY KEY CLUSTERED ([Id])
)') AT [LINKEDSERVERNAME]
This snip it works. dynamically it would be like this DECLARE @object_name SYSNAME
DECLARE @object_id INT
DECLARE @SourceServerName SYSNAME = '[TYLER]'
DECLARE @SourceDBName SYSNAME = '[SOURCEDB]'
DECLARE @SourceSchemaName SYSNAME = 'dbo'
DECLARE @SourceTableName SYSNAME = 'ae420'
DECLARE @TargetServerName SYSNAME = '[TYLER2]'
DECLARE @TargetDBName SYSNAME = '[AdventureWorks]'
DECLARE @TargetSchemaName SYSNAME = 'dbo'
DECLARE @TargetTableName SYSNAME = 'ab149a'
DECLARE @SQL NVARCHAR(MAX)
SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
, @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID( @SourceTableName, 'U')) o
PRINT @object_name
PRINT @object_id
SELECT @SQL = 'exec (''USE ' + @TargetDBName + '; ' + CHAR(13)
SELECT @SQL = @SQL +' CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(13) + ' , [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
ELSE
CASE WHEN c.system_type_id != c.user_type_id
THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
ELSE '[' + UPPER(tp.name) + ']'
END +
CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('nvarchar', 'nchar')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length / 2 AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
THEN ' COLLATE ' + c.collation_name
ELSE ''
END +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END +
CASE WHEN c.default_object_id != 0
THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
ELSE ''
END +
CASE WHEN cc.[object_id] IS NOT NULL
THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
ELSE ''
END +
CASE WHEN c.is_identity = 1
THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
ELSE ''
END
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
ON c.[object_id] = cc.parent_object_id
AND cc.parent_column_id = c.column_id
WHERE c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') +
ISNULL((SELECT '
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' +
CASE WHEN i.index_id = 1
THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END +' (' + (
SELECT STUFF(CAST((
SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
CASE WHEN ic.is_descending_key = 1
THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic WITH(NOLOCK)
WHERE i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
FROM sys.indexes i WITH(NOLOCK)
WHERE i.[object_id] = @object_id AND i.is_primary_key = 1), '') + CHAR(13) + ')'') AT '+ @TargetServerName +''
PRINT @SQL
EXEC (@SQL)
-- this works from executing on source server to creating table on target server.
If anyone has a better solution please share.
If you have a linked server defined than you can refer to a table like this:
select * from linkedserver.database.owne r.table
but if you don't have that you can't use a full qualification name!
Why don't you use the SQL server wizard to migrate a table to a different server? It is a simple and efficient method:
https://technet.microsoft.com/en-ca/library/ms178078(v=sql.105).aspx
select * from linkedserver.database.owne
but if you don't have that you can't use a full qualification name!
Why don't you use the SQL server wizard to migrate a table to a different server? It is a simple and efficient method:
https://technet.microsoft.com/en-ca/library/ms178078(v=sql.105).aspx
ASKER
I am trying to write an automated way of creating and moving data one table at a time on demand rather then having to go through a bunch of steps manually from one server to another. The EXECUTE AT clause executes the build on the remote server using the meta data for the table on the source server/place of original execution. we do not have the luxury of restore database as the data is live.
simplicity and execution with in a stored procedure is where I would like to be for this process, I also find it challenging both to my self and hopefully to the rest of the community on EE.
the syntax for a create table doesn't allow the server database name part to create the table on the remote server neither does the select.. into.. from statement that would create the table. you would get the above mentioned error.
simplicity and execution with in a stored procedure is where I would like to be for this process, I also find it challenging both to my self and hopefully to the rest of the community on EE.
the syntax for a create table doesn't allow the server database name part to create the table on the remote server neither does the select.. into.. from statement that would create the table. you would get the above mentioned error.
In order to do that you will have to generate the code for the table, complete structure and data, on one server and then execute it on the second server. What this procedure does with:
SELECT TOP (0) * INTO [tyler2].[dw].[DBO].[AE420 ] FROM [DBO].[AE420]
it would only work if you have defined a linked server.
SELECT TOP (0) * INTO [tyler2].[dw].[DBO].[AE420
it would only work if you have defined a linked server.
ASKER
That is correct, I have defined linked servers. this is the current script i have working I just need to create the index if there are any.
Any suggestions for attacking the indexing question would be great.
DECLARE @object_name SYSNAME
DECLARE @object_id INT
DECLARE @SourceSchemaName SYSNAME = 'dbo'
DECLARE @SourceTableName SYSNAME = 'ae420'
DECLARE @TargetServerName SYSNAME = '[tyler2]'
DECLARE @TargetDBName SYSNAME = '[AdventureWorks]'
DECLARE @TargetSchemaName SYSNAME = 'dbo'
DECLARE @TargetTableName SYSNAME = 'ab149a'
DECLARE @TargetFilegroup SYSNAME
DECLARE @SQL NVARCHAR(MAX)
SET @SQL ='EXEC (''USE ' + @TargetDBName + '; drop table if exists [' + @TargetSchemaName + '].[' + @TargetTableName + ']'' ) AT '+ @TargetServerName +''
PRINT @SQL
EXEC (@SQL)
SELECT
@object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'
--@object_name = '[' + @TargetSchemaName + '].[' + @TargetTableName + ']'
, @object_id = [object_id]
FROM (SELECT [object_id] = OBJECT_ID( @SourceTableName, 'U')) o
-- Get Filegroup for Table
SELECT TOP 1
@TargetFilegroup = d.name
FROM sys.filegroups d
JOIN sys.indexes i ON i.data_space_id = d.data_space_id
JOIN sys.tables t ON t.object_id = i.object_id
WHERE
t.name= @SourceTableName AND
t.schema_id = schema_id(@SourceSchemaName)
SELECT @SQL = 'exec (''USE ' + @TargetDBName + '; ' + CHAR(13)
-- SELECT @SQL = @SQL +' CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT @SQL = @SQL +' CREATE TABLE [' + @TargetSchemaName + '].[' + @TargetTableName + ']' + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT CHAR(13) + ' , [' + c.name + '] ' +
CASE WHEN c.is_computed = 1
THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)
ELSE
CASE WHEN c.system_type_id != c.user_type_id
THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'
ELSE '[' + UPPER(tp.name) + ']'
END +
CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('nvarchar', 'nchar')
THEN '(' + CASE WHEN c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length / 2 AS VARCHAR(5))
END + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name = 'decimal'
THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id
THEN ' COLLATE ' + c.collation_name
ELSE ''
END +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END +
CASE WHEN c.default_object_id != 0
THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +
' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)
ELSE ''
END +
CASE WHEN cc.[object_id] IS NOT NULL
THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]
ELSE ''
END +
CASE WHEN c.is_identity = 1
THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +
CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'
ELSE ''
END
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.check_constraints cc WITH(NOLOCK)
ON c.[object_id] = cc.parent_object_id
AND cc.parent_column_id = c.column_id
WHERE c.[object_id] = @object_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') +
ISNULL((SELECT '
, CONSTRAINT [' + REPLACE(i.name, @SourceTableName, @TargetTableName) + '] PRIMARY KEY ' +
CASE WHEN i.index_id = 1
THEN 'CLUSTERED'
ELSE 'NONCLUSTERED'
END +' (' + (
SELECT STUFF(CAST((
SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +
CASE WHEN ic.is_descending_key = 1
THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic WITH(NOLOCK)
WHERE i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'
FROM sys.indexes i WITH(NOLOCK)
WHERE i.[object_id] = @object_id AND i.is_primary_key = 1), '') + CHAR(13) + ') on [' + @TargetFilegroup+'] '') AT '+ @TargetServerName +''
PRINT @SQL
EXEC (@SQL)
/*
--populate the table something like this.
BEGIN
declare @s nvarchar(max)
set @s=
'SET IDENTITY_INSERT ' + @TargetTableName + ' ON
INSERT ' + @TargetTableName + ' ( ' +
(SELECT Stuff(( SELECT ', '+FieldName FROM @ShowFields FOR XML PATH('') ), 1, 2, '')) + ')
SELECT * from ' + @SourceTableName + '
SET IDENTITY_INSERT ' + @TargetTableName + ' OFF '
print @s
exec sp_executesql @s
END
*/
Any suggestions for attacking the indexing question would be great.
This script will generate the CREATE statements for all the indexes of all the tables in a database unless you specify a table name:
I edited this comment to store the CREATE statements code into a variable which you can execute in the end. Make sure you check the code.
DECLARE
@table_name varchar='AE420',
@SQL varchar(8000)=''
SELECT I.ID,
REPLICATE(' ',4000) AS COLNAMES ,
OBJECT_NAME(I.ID) AS TABLENAME,
I.ID AS TABLEID,
I.INDID AS INDEXID,
I.NAME AS INDEXNAME,
I.STATUS,
INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,
INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
INTO #TMP
FROM SYSINDEXES I
WHERE I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
and
(
OBJECT_NAME(I.ID)=@table_name
OR
isnull(ltrim(rtrim(@table_name)),'')=''
)
--uncomment below to eliminate PK or UNIQUE indexes;
--what i call 'normal' indexes
--AND INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') =0
--AND INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0
--select * from #TMP
DECLARE
@ISQL VARCHAR(4000),
@TABLEID INT,
@INDEXID INT,
@MAXTABLELENGTH INT,
@MAXINDEXLENGTH INT
--USED FOR FORMATTING ONLY
SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP
DECLARE C1 CURSOR FOR
SELECT TABLEID,INDEXID FROM #TMP
OPEN C1
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @ISQL = ''
SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
WHERE I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
AND I.ID=@TABLEID AND I.INDID=@INDEXID
ORDER BY SYSCOLUMNS.COLID
UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
END
CLOSE C1
DEALLOCATE C1
--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)
SELECT @SQL=@SQL+
'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN ' UNIQUE ' ELSE ' ' END
+ CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE ' ' END
+ ' INDEX [' + UPPER(INDEXNAME) + ']'
+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))
+' ON [' + UPPER(TABLENAME) + '] '
+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME))
+ '(' + UPPER(COLNAMES) + ')'
+ CASE WHEN INDEXFILLFACTOR = 0 THEN '' ELSE ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) END+'
'
FROM #TMP
--SELECT * FROM #TMP
DROP TABLE #TMP
SELECT @SQL /* -- comment this line in order to actually execute the code
EXEC (SQL) --*/
I edited this comment to store the CREATE statements code into a variable which you can execute in the end. Make sure you check the code.
ASKER
Here is the table code to create the table on the testdb
Here is the test data for this table
Can you run on this structure and data, and see what your script yields I get this is all
(0 row(s) affected)
(0 row(s) affected)
USE [testdb]
GO
/****** Object: Table [dbo].[ae420] Script Date: 3/7/2017 1:29:28 PM ******/
DROP TABLE [dbo].[ae420]
GO
/****** Object: Table [dbo].[ae420] Script Date: 3/7/2017 1:29:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ae420](
[Id] [INT] IDENTITY(1,1) NOT NULL,
[grbreknr] [VARCHAR](25) NULL,
[factnr] [VARCHAR](25) NULL,
[cdherkomst] [VARCHAR](25) NULL,
[cdorder] [VARCHAR](25) NULL,
[prijs] [DECIMAL](10, 2) NULL,
[regelbedr] [DECIMAL](10, 2) NULL,
[cdprodukt] [VARCHAR](25) NULL,
[aantgelev] [INT] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170228-145454] ON [dbo].[ae420]
(
[grbreknr] ASC,
[cdherkomst] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO
Here is the test data for this table
exec ('SET IDENTITY_INSERT [AdventureWorks].dbo.ab149a ON;
INSERT INTO [VIC-DATA-DLZ].[AdventureWorks].[dbo].[ab149a]
(Id, grbreknr, factnr, cdherkomst, cdorder, prijs, regelbedr, cdprodukt, aantgelev) Values
(1,''bxdef'', ''abxdef'', '''', ''1'', 1, 10.1, '''', 1),
(2,''axdef'', ''abxdef'', '''', ''31'', 1, 10.2, '''', 1),
(3,''abdef'', ''abxdef'', '''', ''5'', 1, 10.3, '''', 1),
(4,''abxef'', ''abxdef'', '''', ''3'', 1, 10.4, '''', 1);
SET IDENTITY_INSERT [AdventureWorks].dbo.ab149a OFF;') AT [VIC-DATA-DLZ]
Can you run on this structure and data, and see what your script yields I get this is all
(0 row(s) affected)
(0 row(s) affected)
In my code the declare lines have to be like this:
DECLARE
@table_name varchar(255)='ae420',
@SQL varchar(8000)=''
I forgot to specify the size for the @table_name so it would only take the first letter from the string.
DECLARE
@table_name varchar(255)='ae420',
@SQL varchar(8000)=''
I forgot to specify the size for the @table_name so it would only take the first letter from the string.
ASKER
ok, I see it looks good I need to figure out the 4 rows
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC071690F2 72] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-2017022 8-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
CREATE UNIQUE CLUSTERED INDEX [PK_AE420] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-2017022 8-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
as it has only a PK and a nonclustered index. any suggestion? (explanation)
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC071690F2
CREATE INDEX [NONCLUSTEREDINDEX-2017022
CREATE UNIQUE CLUSTERED INDEX [PK_AE420] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-2017022
as it has only a PK and a nonclustered index. any suggestion? (explanation)
From your data sample I only had 2 rows. Are you running it against another table?
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC07227E38 7D] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-2017022 8-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC07227E38
CREATE INDEX [NONCLUSTEREDINDEX-2017022
Check your table see if you didn't have more indexes.
ASKER
Same here:
It must be something in the code you are using. I only get 2 indexes with the code I posted above:
It must be something in the code you are using. I only get 2 indexes with the code I posted above:
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC07227E387D] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-20170228-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
ASKER
complete code used with your example:
USE TYLER
DROP TABLE IF EXISTS #tmp
DECLARE
@table_name varchar(255)='ae420',
@SQL varchar(8000)=''
SELECT I.ID,
REPLICATE(' ',4000) AS COLNAMES ,
OBJECT_NAME(I.ID) AS TABLENAME,
I.ID AS TABLEID,
I.INDID AS INDEXID,
I.NAME AS INDEXNAME,
I.STATUS,
INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,
INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
INTO #TMP
FROM SYSINDEXES I
WHERE I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
and
(
OBJECT_NAME(I.ID)=@table_name
OR
isnull(ltrim(rtrim(@table_name)),'')=''
)
DECLARE
@ISQL VARCHAR(4000),
@TABLEID INT,
@INDEXID INT,
@MAXTABLELENGTH INT,
@MAXINDEXLENGTH INT
--USED FOR FORMATTING ONLY
SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP
DECLARE C1 CURSOR FOR
SELECT TABLEID,INDEXID FROM #TMP
OPEN C1
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @ISQL = ''
SELECT @ISQL=@ISQL + ISNULL(SYSCOLUMNS.NAME,'') + ',' FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
WHERE I.INDID > 0
AND I.INDID < 255
AND (I.STATUS & 64)=0
AND I.ID=@TABLEID AND I.INDID=@INDEXID
ORDER BY SYSCOLUMNS.COLID
UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
END
CLOSE C1
DEALLOCATE C1
--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)
SELECT @SQL=@SQL+
'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN ' UNIQUE ' ELSE ' ' END
+ CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE ' ' END
+ ' INDEX [' + UPPER(INDEXNAME) + ']'
+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))
+' ON [' + UPPER(TABLENAME) + '] '
+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME))
+ '(' + UPPER(COLNAMES) + ')'
+ CASE WHEN INDEXFILLFACTOR = 0 THEN '' ELSE ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) END+'
'
FROM #TMP
--SELECT * FROM #TMP
DROP TABLE #TMP
--SELECT @SQL
PRINT @SQL /* -- comment this line in order to actually execute the code
EXEC (SQL) --*/
ASKER
results of previous posted code.
(4 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC071690F2 72] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-2017022 8-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
CREATE UNIQUE CLUSTERED INDEX [PK_AE420] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-2017022 8-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
(4 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC071690F2
CREATE INDEX [NONCLUSTEREDINDEX-2017022
CREATE UNIQUE CLUSTERED INDEX [PK_AE420] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-2017022
I just used the code you posted on the sable created with the sample code you gave me:
(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC07227E38 7D] ON [AE420] (ID) WITH FILLFACTOR = 95
CREATE INDEX [NONCLUSTEREDINDEX-2017022 8-145454] ON [AE420] (GRBREKNR,CDHERKOMST) WITH FILLFACTOR = 95
(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
CREATE UNIQUE CLUSTERED INDEX [PK__AE420__3214EC07227E38
CREATE INDEX [NONCLUSTEREDINDEX-2017022
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Post the results from that select here. I noticed that in your results message you have:
(4 row(s) affected)
while I have only 2! This is because in your case the script finds 4 rows for indexes, which you will see in that select.
(4 row(s) affected)
while I have only 2! This is because in your case the script finds 4 rows for indexes, which you will see in that select.
ASKER
solution fits the effects I was going for.
How did you call that stored procedure?